Dfrnhjy

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="MagentoFrameworkViewElementUiComponentDataProviderCollectionFactory">
 <plugin name="sales_order_additional_columns" type="VendorSalesPluginAddColumnsSalesOrderGridCollection" />
</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(
 MagentoFrameworkViewElementUiComponentDataProviderCollectionFactory $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;
 

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="MagentoUiComponentListingColumnsDate">

to

<column name="main_table.created_at" class="MagentoUiComponentListingColumnsDate">

Option - 2

Add code to di.xml

<preference for="MagentoSalesModelResourceModelOrderGridCollection" type="VendorYourModuleModelResourceModelOrderGridCollection"/>

Add below code to update collection

<?php
 namespace VendorYourModuleModelResourceModelOrderGrid;

 class Collection extends MagentoSalesModelResourceModelOrderGridCollection
 
 protected function _initSelect()
 

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


 parent::_initSelect();
 
 

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

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