5

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

}
5

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();
        }
    }
  • Please accept answer which really solve your problem. – Suresh Chikani Aug 25 '17 at 12:37
  • 1
    Option 2 is working for me. – Rishabh Rk Rai Dec 27 '17 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')) – Rishabh Rk Rai Jan 2 '18 at 11:38
  • i like option 2 :) – LucScu Mar 8 '18 at 9:43
0

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

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

0

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

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.