2

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>
7
  • Given answer try it, and it will add the data when the next time it post data in sales_order and that will copy into sales_order_grid as well. Commented Mar 5, 2019 at 12:31
  • sure I am trying your solution
    – GenZ Dev
    Commented Mar 5, 2019 at 12:34
  • 1
    the coupon code column is still empty
    – GenZ Dev
    Commented Mar 6, 2019 at 5:21
  • When the next time data is been posted to sales_order then it will get posted in sales_order_grid table as well. Getting it Commented Mar 6, 2019 at 5:21
  • yes I placed a new order and used a coupon code but its not showing in grid. I have updated my question
    – GenZ Dev
    Commented Mar 6, 2019 at 5:23

5 Answers 5

3

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

10
  • @Sufyan, have you tried the above solution?
    – fmsthird
    Commented Mar 6, 2019 at 5:37
  • Do I need to add coupon_code column in sales_order_grid table also?
    – GenZ Dev
    Commented Mar 6, 2019 at 6:23
  • no need , just try the code above and it should work
    – fmsthird
    Commented Mar 6, 2019 at 6:24
  • The coupon code column is empty in grid. I have updated my question also.
    – GenZ Dev
    Commented Mar 6, 2019 at 6:24
  • using code above? have you tried to put data on coupon_code column in your schema?
    – fmsthird
    Commented Mar 6, 2019 at 6:25
1

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 :)

2
  • this solution works only if sales_order_grid already has coupon_code column. You can update your answer.
    – GenZ Dev
    Commented Mar 6, 2019 at 7:39
  • That's what I was saying you need to have column, see the question is to show data in grid. Anyway you got it, finally after the code is been given Commented Mar 6, 2019 at 8:13
1

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

1
  • Thanks for the link. I took the data patch to copy coupon_code from sales_order to sales_order_grid.
    – PauGNU
    Commented Oct 28, 2021 at 13:13
0

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;
0

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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