2

Magento2:Adding column to Sales Order grid table.

6
  • are you trying to add a simple text field as the column or a select type ? – Sourabh Kumar Sharma Sep 29 '18 at 12:16
  • yes i am trying to add simple text fields – Deep Shah Sep 29 '18 at 12:21
  • May i know the version number of your Magento 2 and are you trying to add the eav field into the grid column? – Sourabh Kumar Sharma Sep 29 '18 at 12:28
  • I am using Magento 2.2.5 version and i am going to add simple text fields (like: firstname) – Deep Shah Sep 29 '18 at 12:34
  • Hello @deep please check the answer and let me know if you have any query. – Aditya Shah Sep 29 '18 at 12:42
6
  1. Add column to sales_order_grid database table
  2. Add DI configuration to populate the column in sales_order_grid table with your value
  3. Add UI component configuration to display the column in grid

1.Adding column to sales_order_grid database table

Columns are added to database tables using InstallSchema script.

Just add it to

app/code/<your_namespace>/<your_module>/Setup/InstallSchema.php

file and it will create a column named affiliate_information, of type text, with comment “Affiliate Information” to sales_order_grid during installation.

$setup->getConnection()->addColumn(
    $setup->getTable('sales_order_grid'),
    'affiliate_information',
    [
        'type' => Table::TYPE_TEXT,
        'comment' => 'Affiliate Information'
    ]
);

After this step, affiliate_information column is present in sales_order_grid table, but is remaining empty as it is not mapped to any data source.

2. DI configuration to populate the column is sales_order_grid table.

to include our table in mentioned insert from select, we have to extend di configuration creating

app/code/<Namespace>/<Module>/etc/adminhtml/di.xml

... affiliate entity_id order_id affiliate.affiliate_information ...

After this step, our affiliate_information column in sales_order_grid table is populated with value from affiliate table each time order is placed. Still, column will exist only in database, and will not be visible in admin panel.

3. Configure UI grid component to display the column

Finally, to reflect the column on admin panel grid, we have to extend sales_order_grid ui component by adding a ui configuration file in our module.

app/code///view/adminhtml/ui_component/sales_order_grid.xml

<listing ...>
    <columns name="sales_order_columns">
        <column name="affiliate_information">
            <argument name="data" xsi:type="array">
                <item name="config" xsi:type="array">
                    <item name="filter" xsi:type="string">text</item>
                    <item name="label" xsi:type="string" translate="true">Affiliate Information</item>
                </item>
            </argument>
        </column>
    </columns>
</listing>

This will extend sales_order_columns and add a column based on affiliate_information filed, of type text, with translatable label “Affiliate Information”.

Populating created sales_order_grid column for existing order

For upgrading existing data either install (for first release of your module) or upgrade script should be created.

Here is an example of upgrade script populating sales_order_grid.affiliate_information column from affiliate.affiliate_information for 1.0.1 version of module.

namespace YourNamespace\YourModule\Setup;

use Magento\Framework\Setup\ModuleContextInterface;
use Magento\Framework\Setup\ModuleDataSetupInterface;
use Magento\Framework\Setup\UpgradeDataInterface;

class UpgradeData implements UpgradeDataInterface
{
    /**
     * {@inheritdoc}
     */
    public function upgrade(ModuleDataSetupInterface $setup, ModuleContextInterface $context)
    {
        $setup->startSetup();

        if (version_compare($context->getVersion(), '1.0.1', '<')) {
            $connection = $setup->getConnection();
            $grid = $setup->getTable('sales_order_grid');
            $affiliate = $setup->getTable('affiliate');

            $connection->query(
                $connection->updateFromSelect(
                    $connection->select()
                        ->join(
                            $affiliate,
                            sprintf('%s.entity_id = %s.order_id', $grid, $affiliate),
                            'affiliate_information'
                        ),
                    $grid
                )
            );
        }

        $setup->endSetup();
    }
}

For this upgrade script to be executed, you have to increase module version in module.xml (from 1.0.0 to 1.0.1)*

run bin/magento setup:upgtade command

0

In your module extend sales_order_grid.xml file as: Vendor_Module::view/adminhtml/ui_component/sales_order_grid.xml

Add the column in your sales_order_grid table of the database as per your requirement i.e first_name as per your requirement.. And then use the below code in your custom sales_order_grid.xml :

<listing xmlns:xsi="your namespace code" >
    <column name="first_name">
            <settings>
                <filter>text</filter>
                <label translate="true">First Name</label>
            </settings>
     </column>
</listing>

I have assumed that you have already created the column first_name in your database and have values in it. You just want to get that data in your sales order grid.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.