Tag: sql

In Magento 2 Sometimes we have added a column/field in the table for our custom requirement and in future if we need to rename or change that column name, we can rename it using UpgradeSchema.php file.

I have added one field name guestname in sales_order table and I want to rename that field to fullname. You can rename field using changeColumn() in UpgradeSchema.php file.
Using below code snippet you can rename already existed column field from a table.

Upgrade the setup_version number of module from module.xml file. An older version number is 1.0.1 so I have added new version 1.0.2 in module.xml file

Path: Magento22/app/code/Rbj/Training/etc/module.xml

<?xml version="1.0" ?>
<config xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:noNamespaceSchemaLocation="urn:magento:framework:Module/etc/module.xsd">
	<module name="Rbj_Training" setup_version="1.0.2"/>
</config>

Create UpgradeSchema.php file under Setup folder in your module.Path: Magento22/app/code/Rbj/Training/Setup/UpgradeSchema.php 

<?php
namespace Rbj\Training\Setup;

use Magento\Framework\Setup\ModuleContextInterface;
use Magento\Framework\Setup\SchemaSetupInterface;
use Magento\Framework\Setup\UpgradeSchemaInterface;

/**
 * Upgrade the Sales_Order Table to remove extra field
 */
class UpgradeSchema implements UpgradeSchemaInterface
{

    public function upgrade(SchemaSetupInterface $setup, ModuleContextInterface $context)
    {
        $setup->startSetup();
        if (version_compare($context->getVersion(), '1.0.2', '<')) {
            $setup->getConnection()->changeColumn(
                $setup->getTable('sales_order'),
                'guestname',
                'fullname',
                [
                    'type' => \Magento\Framework\DB\Ddl\Table::TYPE_TEXT,
                    'length' => 255,
                    'comment' => 'Full name of customer'
                ]
            );
        }
        $setup->endSetup();
    }
}

Using the above code snippet you can change column name from guestname to fullname with 255 character type text

Run command from root directory of your project,php bin/magento setup:upgrade

If you want to drop field name from a table, Remove an extra field from a table

If you want to add the field in table  Add New Field in Database Table Magento 2

In Magento There are many conditions to filter specific collection using addAttributeToFilter or addFieldToFilter function.

In Development time you have faced issue related to collection filter based on your requirement you can use below set of conditions in your coding for filter of a collection.  Syntax: addFieldToFilter($field, $condition = null)

Where $field is your table field name.
$condition is your below list of sql condition. Second parameter is string or array.

       Different SQL Conditions
'eq'            => "{{fieldName}} = ?",
	'neq'           => "{{fieldName}} != ?",
	'like'          => "{{fieldName}} LIKE ?",
	'nlike'         => "{{fieldName}} NOT LIKE ?",
	'in'            => "{{fieldName}} IN(?)",
	'nin'           => "{{fieldName}} NOT IN(?)",
	'is'            => "{{fieldName}} IS ?",
	'notnull'       => "{{fieldName}} IS NOT NULL",
	'null'          => "{{fieldName}} IS NULL",
	'gt'            => "{{fieldName}} > ?",
	'lt'            => "{{fieldName}} < ?",
	'gteq'          => "{{fieldName}} >= ?",
	'lteq'          => "{{fieldName}} <= ?",
	'finset'        => "FIND_IN_SET(?, {{fieldName}})",
	'regexp'        => "{{fieldName}} REGEXP ?",
	'from'          => "{{fieldName}} >= ?",
	'to'            => "{{fieldName}} <= ?",
	'seq'           => null,
	'sneq'          => null,
	'ntoa'          => "INET_NTOA({{fieldName}}) LIKE ?"

Example:

$product = $this->productFactory->create()->getCollection()
->addAttributeToFilter('sku', ['eq' => '24-MB01']);

You can use any field type from above to filter your query.