4

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

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.