Tag: Database

Posted on

How to remove extra column from database table using Magento 2?

In Magento 2 Sometimes we have added an extra column in the table for extended functionality in module and in future, we have removed that functionality so we need to remove extra column field from a database table.

I have added one field name gst in sales_order table and I want to remove extra field from table in future. You can remove field using dropColumn() in UpgradeSchema.php file.
Using below code snippet you can remove already existed column field from 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()->dropColumn($setup->getTable('sales_order'), 'gst');
        }
        $setup->endSetup();
    }
}

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

If you want to add the field in table  Add New Field in Database Table Magento 2
If you want to rename/change column field name, Refer link Rename Column name in Table Magento 2

Posted on

Magento addAttributeToFilter different Condition types.

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.

Posted on

Direct SQL Query in Magento 2

There are many situations where executing direct raw SQL queries would be simple and much quicker leading to a more optimized Magento get collection query as a performance basis.

On a large data set of an entity, Saving each individual entity can take a long time with resource-hungry and therefore make the system unusable.

Overcome this issue it is possible to issue a direct SQL query that could update a large set of data of an entity in fewer seconds.

When you use Direct SQL Query you don’t need to worry about Model/Factory Pattern. Continue reading “Direct SQL Query in Magento 2”