5

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.

  • in brief you want to add custom column in sales order grid ?? – Mohammad Mujassam Apr 29 '16 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 Apr 29 '16 at 10:29
  • @MohammadMujassam i think you are also here github.com/magento/magento2/issues/1650 :) – LucScu Apr 29 '16 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 Apr 29 '16 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 .... – Mohammad Mujassam Apr 29 '16 at 10:56
5

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;

    }

}

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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