Category: Magento 2

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

How to import tier pricing-advanced price using csv in Magento 2?

In Magento 2 Import tier pricing is such a simple way.
You need to Create CSV file for import advanced pricing in Magento 2 sometimes Tier price is called as Advanced Pricing.

Tier price is used when you want to give discount on bulk purchase of product whether based on customer group, based on Quantity you purchase.

Go To Admin panel in Magento 2,
Click On System from Left Menu,
System -> Import -> Import Settings -> Advanced Pricing
Select Import Behaviour as Add/Update for first time pricing import for a product,

Below is column name for tier price import CSV
sku Product SKU
tier_price_website Website Code (Default ALL Website)
tier_price_customer_group Customer Group
tier_price_qty No. of Quantity to purchase for getting discount on a price
tier_price_value_type tier price value fixed/discount of the actual price

Download  Tier price CSV for demo, Tier Price CSV

If you want to replace old value of tier price with New You have to select Replace field from Import behavior in Import Tier price section from Admin panel otherwise price value will be added again for product.

Check Default Behaviour of Product import for Add, Replace and Delete function for Product import behavior.

If you don’t know about Import simple product refer link,Import Simple Product Using CSV Magento 2