Magento 2 , How to add "Company Name" column to "Customer Grid"

Question

When we going to create a new order manually from backend, I need to add a "Company Name" column w.r.t. customer. Also need to add filter for the same. Please help.

Answer 1

There are lots of answers about this question. But, almost articles deal with attribute mapped to any field of sales_order table. But, company name is not mapped on sales_order table directly so it 's some complex than others.

This is correct answer which we proved on our site. The detailed steps are as follow.

Adding column to sales_order_grid database table

Columns are added to database tables using UpgradeSchema script. To be consistent, this script should be updated in the same module, where company name table was added.

<?php
   namespace [NameSpace]\[ModuleName]\Setup;

   use Magento\Framework\Setup\ModuleContextInterface;
   use Magento\Framework\Setup\SchemaSetupInterface;
   use Magento\Framework\DB\Ddl\Table;
   use Magento\Framework\Setup\UpgradeSchemaInterface;
   use Magento\Framework\DB\Adapter\AdapterInterface;

 class UpgradeSchema implements UpgradeSchemaInterface
 {

   /**
    * {@inheritdoc}
    * @SuppressWarnings(PHPMD.ExcessiveMethodLength)
   */
   public function upgrade(SchemaSetupInterface $setup, ModuleContextInterface $context)
   {

       $installer = $setup;
       $installer->startSetup();

       $installer->getConnection()->addColumn(
            $installer->getTable('sales_order_grid'),
            'company_name',
            [
                'type'     => Table::TYPE_DATE,
                'nullable' => true,
                'comment'  => 'Company Name',
            ]
        );

       $setup->endSetup();
  }
}

Just add it to app/code///Setup/UpgradeSchema.php file.

DI configuration to populate the column is sales_order_grid table.

When order is placed (according to default configuration), data related to this order is selected from sales_order table joining several additional tables and inserted to sales_order_grid. This operation is initiated by \Magento\Sales\Model\ResourceModel\Grid::refresh function and the default select is declared in “/etc/di.xml” file. app/code/[Namespace]/[Module]/etc/adminhtml/di.xml file.

<virtualType name="Magento\Sales\Model\ResourceModel\Order\Grid" type="Magento\Sales\Model\ResourceModel\Grid">
    <arguments>
        <argument name="joins" xsi:type="array">
             <item name="customer_grid_flat" xsi:type="array">
                <item name="table" xsi:type="string">customer_grid_flat</item>
                <item name="origin_column" xsi:type="string">customer_id</item>
                <item name="target_column" xsi:type="string">entity_id</item>
            </item>
        </argument>
        <argument name="columns" xsi:type="array">
            <item name="company_name" xsi:type="string">customer_grid_flat.company_name</item>                
        </argument>
    </arguments>
</virtualType>

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

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. It is possible to extend ui configuration fo sales order grid introducing app/code/[Namespace]/[Module]/view/adminhtml/ui_component/sales_order_grid.xml

<columns name="sales_order_columns">
    <column name="company_name">
        <argument name="data" xsi:type="array">                
             <item name="js_config" xsi:type="array">
                <item name="component" xsi:type="string">Magento_Ui/js/grid/columns/column</item>
            </item>
            <item name="config" xsi:type="array">
                <item name="visible" xsi:type="boolean">true</item>
                <item name="dataType" xsi:type="string">select</item>
                <item name="align" xsi:type="string">left</item>
                <item name="filter" xsi:type="string">select</item>
                <item name="label" xsi:type="string" translate="true">Company Name</item>
            </item> -->
        </argument>
    </column>
 </columns>

Populating created sales_order_grid column for existing order

To do this, we may create upgrade function as follow.

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');
        $source = $setup->getTable('customer_grid_flat');

        $connection->query(
            $connection->updateFromSelect(
                $connection->select()
                    ->join(
                        $source,
                        sprintf('%s.customer_id = %s.entity_id', $grid, $source),
                        'company_name'
                    ),
                $grid
            )
        );
    }

    $setup->endSetup();
}

Finally, be sure to refresh config cache after editing xml files.

The importance of this answer is just dealing with possibility of all kinds of tables, not just sales_order table records.

Hope this answer is useful for you. Please feel free to provide any feedback in comments.