How to sync custom columns from sales_order to sales_order_grid in magento 2?

Question

I create 2 custom columns in sales_order table and sales_order_grid tables.

As far as I know columns will be sync by cron job if name of both tables are same.

So I run a cron but data from sales_order table dont sync with grid table. What could I forget?

In my di.xml file I put this code:

<!-- Display date in grid   -->
<virtualType name="Magento\Sales\Model\ResourceModel\Order\Grid" type="Magento\Sales\Model\ResourceModel\Grid">
    <arguments>
        <argument name="columns" xsi:type="array">
            <item name="custom1" xsi:type="string">sales_order.custom1</item>
            <item name="custom2" xsi:type="string">sales_order.custom2</item>
        </argument>
    </arguments>
</virtualType>

Answer 1

I had a similar issue recently: I created a new order attribute using VirtualTypes and it was not being mapped into the sale_order_grid column.

It was necessary to create a Patch to create an initial mapping. After that, it started getting mapped (I don't why exactly).

If you want to give it a try, create a new Patch:

• In your module folder Vendor/Module/Setup/Patch/Data/CopyOrderFieldToGrid.php

• And the file CopyOrderFieldToGrid.php contains (pay attention at the version module, it has to be higher than your version in module.xml; pay also attention at the condition in the 'where' clause, you might need to change it)

<?php

namespace Vendor\Module\Setup\Patch\Data;

use Magento\Framework\App\State;
use Magento\Framework\Setup\Patch\DataPatchInterface;
use Magento\Framework\Setup\Patch\PatchVersionInterface;
use Magento\Framework\Setup\ModuleDataSetupInterface;

/**
 * Class FillOrderColumnsInOrderGridColumns
 */
class CopyOrderFieldToGrid implements DataPatchInterface, PatchVersionInterface
{
    /**
     * @var ModuleDataSetupInterface
     */
    private $moduleDataSetup;

    /**
     * @var State
     */
    private $state;

    /**
     * PatchInitial constructor.
     * @param ModuleDataSetupInterface $moduleDataSetup
     */
    public function __construct(
        ModuleDataSetupInterface $moduleDataSetup,
        State $state
    ) {
        $this->moduleDataSetup = $moduleDataSetup;
        $this->state = $state;
    }

    /**
     * {@inheritdoc}
     */
    public static function getDependencies()
    {
        return [];
    }

    /**
     * {@inheritdoc}
     */
    public static function getVersion()
    {
        return '5.0.0';
    }

    /**
     * {@inheritdoc}
     */
    public function apply()
    {
        $this->state->emulateAreaCode(
            \Magento\Backend\App\Area\FrontNameResolver::AREA_CODE,
            [$this, 'fillOrderColumnsInOrderGridColumns'],
            [$this->moduleDataSetup]
        );
    }

    /**
     * Fill Order Columns In Order Grid Columns.
     *
     * @param ModuleDataSetupInterface $setup
     */
    public function fillOrderColumnsInOrderGridColumns(ModuleDataSetupInterface $setup)
    {
        $gridTable = $setup->getTable('sales_order_grid');
        $updateOrderGrid = $setup->getConnection()
            ->select()
            ->joinInner(
                ['sales_order' => $setup->getTable('sales_order')],
                $gridTable . '.entity_id = sales_order.entity_id',
                ['your_order_attribute' => 'sales_order.your_order_attribute']
            )
            ->where(
                'sales_order.your_order_attribute IS NOT NULL'
            );

        $updateOrderGrid = $setup->getConnection()->updateFromSelect(
            $updateOrderGrid,
            $gridTable
        );

        $setup->getConnection()->query($updateOrderGrid);
    }

    /**
     * {@inheritdoc}
     */
    public function getAliases()
    {
        return [];
    }
}