6

I added sku, country_code and product name in the order grid. All works fine but when I filter Orders by Created at it throws following error

Exception #0 (Zend_Db_Statement_Exception): SQLSTATE[23000]: Integrity constraint violation: 1052 Column 'created_at' in where clause is ambiguous, query was: SELECT COUNT(DISTINCT soa.parent_id) FROM sales_order_grid AS main_table INNER JOIN sales_order_item AS soi ON main_table.entity_id = soi.order_id INNER JOIN sales_order_address AS soa ON main_table.entity_id = soa.parent_id WHERE (created_at >= '2017-07-31 23:00:00') AND (created_at <= '2017-08-25 22:59:59')

My code looks like this

di.xml

<type name="Magento\Framework\View\Element\UiComponent\DataProvider\CollectionFactory">
    <plugin name="sales_order_additional_columns" type="Vendor\Sales\Plugin\AddColumnsSalesOrderGridCollection" />
</type> 

view/adminhtml/ui-component/sales_order_grid.xml

<column name="sku">
        <argument name="data" xsi:type="array">
            <item name="config" xsi:type="array">
                <item name="label" xsi:type="string" translate="true">Sku</item>
                <item name="filter" xsi:type="string">text</item>
                <item name="visible" xsi:type="boolean">true</item>
            </item>
        </argument>
    </column>
    <column name="name">
        <argument name="data" xsi:type="array">
            <item name="config" xsi:type="array">
                <item name="label" xsi:type="string" translate="true">Product Name</item>
                <item name="filter" xsi:type="string">text</item>
                <item name="visible" xsi:type="boolean">true</item>
            </item>
        </argument>
    </column>

and AddColumnsSalesOrderGridCollection.php

public function aroundGetReport(
    \Magento\Framework\View\Element\UiComponent\DataProvider\CollectionFactory $subject,
    \Closure $proceed,
    $requestName
) {
    $result = $proceed($requestName);
    if ($requestName == 'sales_order_grid_data_source') {
        if ($result instanceof $collection) {
            $collection->getSelect()->join(
                ["soi" => "sales_order_item"],
                'main_table.entity_id = soi.order_id',
                ['sku' => 'GROUP_CONCAT(DISTINCT soi.sku)',
                 'name' => 'GROUP_CONCAT(DISTINCT soi.name)'
                ]
            )->join(
                ["soa" => "sales_order_address"],
                'main_table.entity_id = soa.parent_id',
                ['country_id' => 'soa.country_id']
            )->group("soa.parent_id");
        }
        return $collection;
    }

}

6 Answers 6

11

You can achieve this task by below 2 option

Option - 1

You need to change 'created_at' column in

view/adminhtml/ui-component/sales_order_grid.xml

Change column

<column name="created_at" class="Magento\Ui\Component\Listing\Columns\Date">

to

<column name="main_table.created_at" class="Magento\Ui\Component\Listing\Columns\Date">

Option - 2

Add code to di.xml

<preference for="Magento\Sales\Model\ResourceModel\Order\Grid\Collection" type="Vendor\YourModule\Model\ResourceModel\Order\Grid\Collection"/>

Add below code to update collection

<?php
    namespace Vendor\YourModule\Model\ResourceModel\Order\Grid;

    class Collection extends \Magento\Sales\Model\ResourceModel\Order\Grid\Collection
    {
        protected function _initSelect()
        {

            $this->addFilterToMap('created_at', 'main_table.created_at');


            parent::_initSelect();
        }
    }
6
  • Please accept answer which really solve your problem. Aug 25, 2017 at 12:37
  • 2
    Option 2 is working for me. Dec 27, 2017 at 6:15
  • I got an error when export order from selecting orders from checkbox - Integrity constraint violation: 1052 Column 'entity_id' in where clause is ambiguous, query was: SELECT COUNT(DISTINCT soi.order_id) FROM sales_order_grid AS main_table INNER JOIN sales_order_item AS soi ON main_table.entity_id = soi.order_id LEFT JOIN sales_order_address ON main_table.entity_id = sales_order_address.parent_id WHERE (entity_id IN('147')) Jan 2, 2018 at 11:38
  • 1
    i like option 2 :)
    – LucScu
    Mar 8, 2018 at 9:43
  • Wow !! Super answer and actually I used this solution in newsletter_subscriber admin grid and it worked. Feb 3, 2020 at 8:38
4

Update: I have created a patch to fix this issue. I hope this will help you.

diff --git a/vendor/magento/module-sales/Plugin/Model/ResourceModel/Order/OrderGridCollectionFilter.php b/vendor/magento/module-sales/Plugin/Model/ResourceModel/Order/OrderGridCollectionFilter.php index 995bb8335..cff4b8971 100644
--- a/vendor/magento/module-sales/Plugin/Model/ResourceModel/Order/OrderGridCollectionFilter.php
+++ b/vendor/magento/module-sales/Plugin/Model/ResourceModel/Order/OrderGridCollectionFilter.php @@ -52,7 +52,7 @@ class OrderGridCollectionFilter
                 }
             }
 
-            $fieldName = $subject->getConnection()->quoteIdentifier($field);
+            $fieldName = $subject->getConnection()->quoteIdentifier('main_table.' . $field);
             $condition = $subject->getConnection()->prepareSqlCondition($fieldName, $condition);
             $subject->getSelect()->where($condition, null, Select::TYPE_CONDITION);
3

Solution: Nested Select Queries

As an advantage, nesting subqueries allows to only include the required data. This greatly reduces the risk of conflicts while resulting in a lighter dataset which can make a significant difference on large tables.

However, a simple join without nested query should perform better in cases where the amount of data doesn't differ much once the unrequired columns have been excluded.

Part of the issue is caused by the fact that when using SELECT COUNT(), it doesn't take into consideration the selected columns submitted through the third parameter with the joinLeft() function and therefore all columns end up included in the results.

public function aroundGetReport(
    \Magento\Framework\View\Element\UiComponent\DataProvider\CollectionFactory $subject,
    \Closure $proceed,
    $requestName
) {

    $result = $proceed($requestName);
    if ($requestName == 'sales_order_grid_data_source') {
        if ($result instanceof $collection) {

            $soi = new \Zend_Db_Expr('(SELECT order_id, sku, name from '.$collection->getTable('sales_order_item').')');

            $soa = new \Zend_Db_Expr('(SELECT parent_id, country_id from '.$collection->getTable('sales_order_address').')');

            $collection->getSelect()->join(
                ["soi" => $soi],
                'main_table.entity_id = soi.order_id',
                ['sku' => 'GROUP_CONCAT(DISTINCT soi.sku)', 'name' => 'GROUP_CONCAT(DISTINCT soi.name)']
            )->join(
                ["soa" => $soa],
                'main_table.entity_id = soa.parent_id',
                ['country_id' => 'soa.country_id']
            )->group("entity_id");
        }

        return $collection;
    }

}

Note: Although the addFilterToMap() function might resolve the issue where there is very few columns implicated, one have to take into consideration that any extension added in the future might bring additional columns which could result in additional conflicts.

Reference - OP's Original Post on GitHub: https://github.com/magento/magento2/issues/10657

2

Even I had an issue with similar error for:

SQLSTATE[23000]: Integrity constraint violation: 1052 Column 'created_at' in where clause is ambiguous

The simple code helped me to overcome the issue, just added the code below to the $collection and the column ambiguity went away:

$collection->addFilterToMap('created_at', 'main_table.created_at')

Please try if this helps anyone...

1

You are getting this error because created_at column present in both the tables (sales_order_grid & sales_order_item).

So magento not identify column from which table you are use.

Need to modify where condition something like below. If you run below query directly in PhpMyAdmin, It is working fine.

SELECT COUNT(DISTINCT soa.parent_id) FROM sales_order_grid AS main_table INNER JOIN sales_order_item AS soi ON main_table.entity_id = soi.order_id INNER JOIN sales_order_address AS soa ON main_table.entity_id = soa.parent_id WHERE (**main_table.created_at** >= '2017-07-31 23:00:00') AND (**main_table.created_at** <= '2017-08-25 22:59:59')
0

Add this code in applyFilters() fuction.

Path : vendor/magento/framework/View/Element/UiComponent/DataProvider/FilterPool.php

$select = $collection->getSelect();
       $where = $select->getPart('where');
       foreach ($where as &$item) {
          if (strpos($item, '(`created_at`') !== false) {
             $item = str_replace('`created_at`', '`main_table`.`created_at`', $item);
          }
       }
$select->setPart('where', $where);

Complete code

public function applyFilters(Collection $collection, SearchCriteriaInterface $criteria)
    {
        $groupedParts = $collection->getSelect()->getPart(Select::WHERE);
        foreach ($criteria->getFilterGroups() as $filterGroup) {
            $filterParts = [];
            foreach ($filterGroup->getFilters() as $filter) {
                $filterApplier = $this->appliers[$filter->getConditionType()] ?? $this->appliers['regular'];
                $filterApplier->apply($collection, $filter);
                $whereParts = $collection->getSelect()->getPart(Select::WHERE);
                if (is_array($whereParts) && count($whereParts)) {
                    $appliedParts = array_diff($whereParts, $groupedParts);
                    foreach ($appliedParts as $part) {
                        $filterParts[] = $this->preparePart($part);
                    }
                }
                $collection->getSelect()->reset(Select::WHERE);
                $collection->getSelect()->setPart(Select::WHERE, $groupedParts);
            }
            if (count($filterParts)) {
                $resultCondition = '((' . implode(') ' . Select::SQL_OR . ' (', $filterParts) . '))';
                $groupedParts[] = (count($groupedParts) ? Select::SQL_AND : '') . ' ' . $resultCondition;
                $collection->getSelect()->setPart(Select::WHERE, $groupedParts);
            }
        }
        if (count($groupedParts)) {
            $collection->getSelect()->setPart(Select::WHERE, $groupedParts);
        }

        $select = $collection->getSelect();
       $where = $select->getPart('where');
       foreach ($where as &$item) {
          if (strpos($item, '(`created_at`') !== false) {
             $item = str_replace('`created_at`', '`main_table`.`created_at`', $item);
          }
       }
       $select->setPart('where', $where);      

    }

Note: You can create patch or override.

Your Answer

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

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