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">
        <argument name="mainTable" xsi:type="string">sales_order_grid</argument>
        <argument name="resourceModel" xsi:type="string">Magento\Sales\Model\ResourceModel\Order</argument>

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.

  • in brief you want to add custom column in sales order grid ?? Commented Apr 29, 2016 at 10:23
  • no, i want to add a where clause. this is the original query SELECT main_table.* FROM sales_order_grid AS main_table, i have to change it to SELECT main_table.* FROM sales_order_grid AS main_table WHERE .........
    – LucScu
    Commented Apr 29, 2016 at 10:29
  • @MohammadMujassam i think you are also here github.com/magento/magento2/issues/1650 :)
    – LucScu
    Commented Apr 29, 2016 at 10:43
  • it's incredible that from yesterday i try to find how to do this simple things that for m1 was very ease.
    – LucScu
    Commented Apr 29, 2016 at 10:44
  • yes I did achieved that but I had to add one custom column in sales_order_grid table, that was pretty easy, your case is bit different I believe ? but if its just matter of one or two column, I would recommend you to add you custom column in sales_order_grid, because every day no of sales will be added & running query for that will impact on performance .... Commented Apr 29, 2016 at 10:56

1 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"/>

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;



Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.