Apply Filter not working on Magento 2 for custom column Sales Order

Question

I have save my custom data in quote and sales_order table and show data in Admin grid but filter is not working in this as it is search in sales_order_grid table in Magento 2.

my 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="export_status" class="[COMPANY]\[MODULE]\Ui\Component\Listing\Column\Status">
        <argument name="data" xsi:type="array">
            <item name="config" xsi:type="array">
                <item name="visible" xsi:type="boolean">true</item>
                <item name="label" xsi:type="string" translate="true">XML Exported</item>
            </item>
        </argument>
    </column>
</columns>

Then created the UI class under [COMPANY]/[MODULE]/Ui/Component/Listing/Column/Status.php

namespace [COMPANY]\[MODULE]\Ui\Component\Listing\Column;

use \Magento\Sales\Api\OrderRepositoryInterface;
use \Magento\Framework\View\Element\UiComponent\ContextInterface;
use \Magento\Framework\View\Element\UiComponentFactory;
use \Magento\Ui\Component\Listing\Columns\Column;
use \Magento\Framework\Api\SearchCriteriaBuilder;

class Status extends Column
{
protected $_orderRepository;
protected $_searchCriteria;

public function __construct(ContextInterface $context, UiComponentFactory $uiComponentFactory, OrderRepositoryInterface $orderRepository, SearchCriteriaBuilder $criteria, array $components = [], array $data = [])
{
    $this->_orderRepository = $orderRepository;
    $this->_searchCriteria  = $criteria;
    parent::__construct($context, $uiComponentFactory, $components, $data);
}

public function prepareDataSource(array $dataSource)
{
    if (isset($dataSource['data']['items'])) {
        foreach ($dataSource['data']['items'] as & $item) {

            $order  = $this->_orderRepository->get($item["entity_id"]);
            $status = $order->getData("export_status");

            switch ($status) {
                case "0":
                    $export_status = "No";
                    break;
                case "1";
                    $export_status = "Yes";
                    break;
                default:
                    $export_status = "Failed";
                    break;

            }

            // $this->getData('name') returns the name of the column so in this case it would return export_status
            $item[$this->getData('name')] = $export_status;
        }
    }

    return $dataSource;
}
}

But my Filters are not working how to get it worked.

Any help is appreciated.

Answer 1

The filters only works with fields that exists in sales_order_grid table, in order to use filter option, you need to add the columns in sales_order_grid table and link sales_order columns to sales_order_grid columns by editing your di.xml file with a virtual type

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