Magento 2: admin sales grid, filter column from sales_order table

Question

I have added a column "rescue_order_name" in sales_order table. Now i want to display the values of this column in the admin panel, sales, order grid and give the option to the admin to filter certain record on the basis of this field

I have been able to display the column in the sales grid

<columns name="sales_order_columns">
    <column name="rescue_order_name" class="\Rescuechimp\OrderManager\Ui\Component\Listing\Column\Ordername">
        <argument name="data" xsi:type="array">
            <item name="config" xsi:type="array">
                <item name="filter" xsi:type="string">text</item> 
                <item name="sortable" xsi:type="boolean">false</item>
                <item name="label" xsi:type="string" translate="true">Order Name</item>
            </item>
        </argument>
    </column>
</columns>

and in my Class

 public function prepareDataSource(array $dataSource) {
    if (isset($dataSource['data']['items'])) {
        foreach ($dataSource['data']['items'] as & $item) {
            $order = $this->_orderRepository->get($item["entity_id"]);
            $rescueName = $order->getData("rescue_order_name");
            $item['rescue_order_name'] = $rescueName;
        }
    }

    return $dataSource;
}

I am able to get the correct values, but at the moment, i am unable to use the filters for this property.

No matter what i type in this Order Name field, my result remain the same and nothing gets filtered.

I do not want to add this column in the sales_order_grid table.

Answer 1

You need to join your new field rescue_order_name to order grid collection first.

First override Magento\Sales\Model\ResourceModel\Order\Grid\Collection and add below function to join.

protected function _initSelect()
    {
        parent::_initSelect();

        $this->getSelect()->joinLeft(
            ['so' => $this->getTable('sales_order')],
            'main_table.entity_id = so.entity_id',
            ['rescue_order_name']
        );

        return $this;
    }

And you can remove class="\Rescuechimp\OrderManager\Ui\Component\Listing\Column\Ordername" from sales grid xml for your rescue_order_name column.

I hope this might help you it works fine to me.

Let me know if you need any more help.