Add data to custom order attribute Magento 2

Question

I have set up a custom field in my admin grid. I am successfully executing following Observer:

<?php
namespace Qxs\OrderedItems\Observer;

class SetOrderAttribute implements \Magento\Framework\Event\ObserverInterface
{
    /**
     *
     * @param \Magento\Framework\Event\Observer $observer
     */
    public function execute(\Magento\Framework\Event\Observer $observer)
    {
      $order = $observer->getOrder();
      $order->setOrderedItems('Test');
      return $this;
    }
}

The observer is triggered on event: sales_order_save_before However the data is stored in the table sales_order and not in the table sales_order_grid .

As a result the order grid does not show the value. How can i edit the order data so it will be updated in sales_order_grid?

Answer 1

Step 1 - Sync the column "my_column" between the sales_order and sales_order_grid tables . Add below code in your module's di.xml - Namespace/Modulename/etc/di.xml

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

Step 2 - Introduce the new column in sales_order_grid table. Use your module's UpradeSchema.php/InstallSchema.php class to add the new column

$setup->getConnection()->addColumn(
        $setup->getTable('sales_order_grid'),
        'my_column',
        [
            'type' => \Magento\Framework\DB\Ddl\Table::TYPE_TEXT,
            'length' => 255,
            'nullable' => true,
            'comment' => 'My Column'
        ]
    );

Magento 2.3.4 and later support below code Namespace/Modulename/etc/db_schema.xml

<?xml version="1.0"?>

<schema xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
        xsi:noNamespaceSchemaLocation="urn:magento:framework:Setup/Declaration/Schema/etc/schema.xsd">
    <table name="sales_order_grid" resource="sales" engine="innodb" comment="My Column">
        <column xsi:type="varchar" name="my_column" nullable="true" length="255" comment="My Column"/>
    </table>
</schema>

Step 3 - Add the column "my_column" (My Column) to the sales order grid. Namespace/Modulename/view/adminhtml/ui_component/sales_order_grid.xml

<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="my_column">
        <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">My Column</item>
            </item>
        </argument>
    </column>
</columns>