markshust/magento2-module-ordergrid

Integrity constraint violation: 1052 Column 'created_at' in where clause is ambiguous,

infoconic opened this issue · 1 comments

successfully Installed this extension on magento version 2.2 and its working like expected. I also added a functionality to display SKU of product names and its working perfectly fine.

Issue - when i apply filters based on date selected, it shows "Something went wrong with processing the default view and we have restored the filter to its original state."

i checked magento exception.log and found this error.

[2020-01-20 08:11:55] main.CRITICAL: SQLSTATE[23000]: Integrity constraint violation: 1052 Column 'created_at' in where clause is ambiguous, query was: SELECT COUNT(DISTINCT main_table.entity_id) FROM sales_order_grid AS main_table
INNER JOIN sales_order_item ON main_table.entity_id = sales_order_item.order_id WHERE (created_at >= '2019-10-01 04:00:00') AND (created_at <= '2019-02-01 04:59:59') AND (status = 'processing') {"exception":"[object] (Zend_Db_Statement_Exception(code: 23000): SQLSTATE[23000]: Integrity constraint violation: 1052 Column 'created_at' in where clause is ambiguous, query was: SELECT COUNT(DISTINCT main_table.entity_id) FROM sales_order_grid AS main_table
INNER JOIN sales_order_item ON main_table.entity_id = sales_order_item.order_id WHERE (created_at >= '2019-10-01 04:00:00') AND (created_at <= '2019-02-01 04:59:59') AND (status = 'processing') at /home/jendei5/public_html/vendor/magento/zendframework1/library/Zend/Db/Statement/Pdo.php:235, PDOException(code: 23000): SQLSTATE[23000]: Integrity constraint violation: 1052 Column 'created_at' in where clause is ambiguous at /home/jendei5/public_html/vendor/magento/zendframework1/library/Zend/Db/Statement/Pdo.php:228)"} []

Just found a solution and its fixed. The column "created_at" exist in more than 1 tables of magento database, so we need to define the table name as well.
The magento can not identify column from which table we want to fetch data.

Added code to di.xml of extension
<preference for="Magento\Sales\Model\ResourceModel\Order\Grid\Collection" type="Vendor\YourModule\Model\ResourceModel\Order\Grid\Collection"/>

and then below code to function _initSelect()
$this->addFilterToMap('created_at', 'main_table.created_at');

after this implementation, its working and no more error "something went wrong" !