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

Question

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;
    }

}

Answer 1

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();
        }
    }

Answer 2

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);

Answer 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

Answer 4

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...

Answer 5

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')

Answer 6

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.