How to customize admin sales order grid collection?

Question

I have to add a custom where clause to the query that retrieve data to fill sales order in the relative admin grid. I know that it is defined here \vendor\magento\module-sales\etc\di.xml as virtualType

<virtualType name="Magento\Sales\Model\ResourceModel\Order\Grid\Collection" type="Magento\Framework\View\Element\UiComponent\DataProvider\SearchResult">
    <arguments>
        <argument name="mainTable" xsi:type="string">sales_order_grid</argument>
        <argument name="resourceModel" xsi:type="string">Magento\Sales\Model\ResourceModel\Order</argument>
    </arguments>
</virtualType>

and \vendor\magento\framework\View\Element\UiComponent\DataProvider\DataProvider.php is used to retrieve data, but where and how could i use _initSelect() or _renderFiltersBefore() discussed here How to join order grid collection to custom table in Magento2? to change query?

In magento 1 i did it with the filter_condition_callback option for addColumn() in customized grid file.

Answer 1

You could use a plugin on Magento/Framework/View/Element/UiComponent/DataProvider/Reporting and its search() method.

Just create di.xml in your custom module Vendor/Module/etc/adminhtml/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\Reporting">
        <plugin name="sales_grid_collection" type="Vendor\Module\Model\Plugin\Sales\Order\Grid"/>
    </type>
</config>

and plugin

namespace Vendor\Module\Model\Plugin\Sales\Order;

class Grid {

    public static $table = 'sales_order_grid';


    /**
     *
     */
    public function afterSearch($intercepter, $collection) {

        if ($collection->getMainTable() === $collection->getConnection()->getTableName(self::$table)) {      

            // retrieve where clause
            $where = $collection->getSelect()->getPart(\Magento\Framework\DB\Select::WHERE);

            // ...
            // works with $where
            // ...

            // set the new where clause    
            $collection->getSelect()->setPart(\Magento\Framework\DB\Select::WHERE, $where);                

        }

        return $collection;

    }

}