Magento2 : Add sales_order column coupon_code in sales order grid

Question

In sales_order table we have a column coupon_code. I need to show it in sales order grid.

I have added the column coupon code by overriding sales_order_grid.xml file. The column is being shown. Now I need to show the values of sales_order.coupon_code.

sales_order_grid.xml:

    <?xml version="1.0" encoding="UTF-8"?>
<listing xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:noNamespaceSchemaLocation="urn:magento:module:Magento_Ui:etc/ui_configuration.xsd">
    <columns name="sales_order_columns">
        <column name="coupon_code">
            <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">text</item>
                <item name="align" xsi:type="string">left</item>
                <item name="label" xsi:type="string" translate="true">Coupon Code</item>
            </item>
        </argument>
        </column>
    </columns>
</listing>  

Module/etc/di.xml:

<?xml version="1.0"?>                                                   
<config xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:noNamespaceSchemaLocation="urn:magento:framework:ObjectManager/etc/config.xsd">
<virtualType name="Magento\Sales\Model\ResourceModel\Order\Grid" type="Magento\Sales\Model\ResourceModel\Grid">
    <arguments>
        <argument name="columns" xsi:type="array">
            <item name="coupon_code" xsi:type="string">sales_order.coupon_code</item>
        </argument>
    </arguments>
</virtualType>  
</config>

Answer 1

EDIT:

Add in or add an InstallSchema.php from this path of your custom module:

Vendor/Module/Setup/InstallSchema.php

<?php

namespace vendor\module\Setup;

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

class InstallSchema implements InstallSchemaInterface
{

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

    $installer->startSetup();

    /* While module install, creates column in sales_order_grid table */

    $eavTable = $installer->getTable('sales_order_grid');

    $columns = [
        'coupon_code' => [
            'type' => \Magento\Framework\DB\Ddl\Table::TYPE_TEXT,
            'nullable' => true,
            'comment' => 'Coupon Code',
        ],
    ];

    $connection = $installer->getConnection();
    foreach ($columns as $name => $definition) {
        $connection->addColumn($eavTable, $name, $definition);
       }

    $installer->endSetup();
}
}

Then after, you have to map the coupon_code column to sales_order column in your di.xml configuration:

<virtualType name="Magento\Sales\Model\ResourceModel\Order\Grid" type="Magento\Sales\Model\ResourceModel\Grid">
    <arguments>
        <argument name="columns" xsi:type="array">
            <item name="coupon_code" xsi:type="string">sales_order.coupon_code</item>
        </argument>
    </arguments>
</virtualType>  

Then in your sales_order_grid.xml

<?xml version="1.0" encoding="UTF-8"?>
<listing xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:noNamespaceSchemaLocation="urn:magento:module:Magento_Ui:etc/ui_configuration.xsd">
    <columns name="sales_order_columns">
        <column name="coupon_code">
            <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">text</item>
                <item name="align" xsi:type="string">left</item>
                <item name="label" xsi:type="string" translate="true">Coupon Code</item>
            </item>
        </argument>
        </column>
    </columns>
</listing>   

Note: make sure to run commands after performing changes above:
setup:upgrade
setup:static-content:deploy -f

Answer 2

Try this,

Add di.xml in

app/code/vendor/module/etc/di.xml

add the below code in it

<?xml version="1.0"?>                                                   
<config xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:noNamespaceSchemaLocation="urn:magento:framework:ObjectManager/etc/config.xsd">
<virtualType name="Magento\Sales\Model\ResourceModel\Order\Grid" type="Magento\Sales\Model\ResourceModel\Grid">
<arguments>                                                           
<argument name="columns">
        <item name="salesperson" xsi:type="string">sales_order.coupon_code</item>
</argument>
</arguments>                                                    
</virtualType>
</config>

Hope this helps :)

Answer 3

For new orders via Virtual type definition: - di.xml above ( copy on the fly values from sales_order table into sales_order_grid )

For exists orders: I didn't find advice, so solution you can find below.

Solution: We need copy values from sales_order table into sales_order_grid table.

Following Magento 2.3* practice we will do it during module installation from Setup/Patch/Data of your module.

Implementation please find from github: https://github.com/iop/magento2-order-grid.git

Answer 4

I know this is old, but if you want to copy values from sales_order to sales_order grid, you can also do it using a sql query:

UPDATE sales_order_grid sog 
  JOIN sales_order so 
    ON sog.increment_id = so.increment_id 
  SET sog.coupon_code = so.coupon_code;

Answer 5

This approach won't require any database update and there is very few code to write. Tested with zero issues in a 2.4 project

app/code/Vendor/Module/etc/di.xml

<?xml version="1.0"?>
<config xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
        xsi:noNamespaceSchemaLocation="urn:magento:framework:ObjectManager/etc/config.xsd">
    <type name="Magento\Framework\View\Element\UiComponent\DataProvider\CollectionFactory">
        <arguments>
            <argument name="collections" xsi:type="array">
                <item name="sales_order_grid_data_source" xsi:type="string">Vendor\Module\Model\ResourceModel\Order\Grid\Collection</item>
            </argument>
        </arguments>
    </type>

    <type name="Vendor\Module\Model\ResourceModel\Order\Grid\Collection">
        <arguments>
            <argument name="mainTable" xsi:type="string">sales_order_grid</argument>
            <argument name="resourceModel" xsi:type="string">Magento\Sales\Model\ResourceModel\Order</argument>
        </arguments>
    </type>
</config>

app/code/Vendor/Module/Model/ResourceModel/Order/Grid/Collection.php

<?php
namespace Vendor\Module\Model\ResourceModel\Order\Grid;

use Magento\Framework\Data\Collection\Db\FetchStrategyInterface as FetchStrategy;
use Magento\Framework\Data\Collection\EntityFactoryInterface as EntityFactory;
use Magento\Framework\Event\ManagerInterface as EventManager;
use Magento\Sales\Model\ResourceModel\Order;
use Magento\Sales\Model\ResourceModel\Order\Grid\Collection as OrderGridCollection;
use Psr\Log\LoggerInterface as Logger;

class Collection extends OrderGridCollection
{
    public function __construct(
        EntityFactory $entityFactory,
        Logger $logger,
        FetchStrategy $fetchStrategy,
        EventManager $eventManager,
        $mainTable = 'sales_order_grid',
        $resourceModel = Order::class
    ) {
        parent::__construct($entityFactory, $logger, $fetchStrategy, $eventManager, $mainTable, $resourceModel);
    }

    protected function _renderFiltersBefore()
    {
        $joinTable = $this->getTable('sales_order');
        $this->getSelect()->joinLeft(
            ['sales_order_table' => $joinTable],
            'main_table.entity_id = sales_order_table.entity_id',
            ['coupon_code'] // you can add other attributes here
        );

        parent::_renderFiltersBefore();
    }
}

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

<?xml version="1.0" encoding="UTF-8"?>
<listing xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:noNamespaceSchemaLocation="urn:magento:module:Magento_Ui:etc/ui_configuration.xsd">
    <columns name="sales_order_columns">
        <column name="coupon_code">
            <settings>
                <filter>text</filter>
                <label translate="true">Coupon Code</label>
            </settings>
        </column>
    </columns>
</listing>

Don't forget to add the required module definition (etc/module.xml & registration.php) files

Credits to https://amasty.com/knowledge-base/how-to-add-a-custom-column-to-the-order-grid-in-magento-2.html