magento2: OrderGridCollectionFilter Plugin Breaks Mapping for `created_at` Column

Preconditions and environment

Magento 2.4.5-p1

Steps to reproduce

Add an afterSearch plugin on Magento\Framework\View\Element\UiComponent\DataProvider\Reporting that joins the sales_order table to the sales_order_grid table.

Example:

$salesOrderTable = $collection->getConnection()->getTableName('sales_order');
            $collection->getSelect()->joinLeft(
                ['sales' => $salesOrderTable],
                'sales.increment_id = main_table.increment_id',
                'your_field'
            );

Filter the Order grid on purchase date.

Expected result

The orders are filtered by purchase date.

Actual result

The order grid does not filter on purchase date
An alert box says, “Attention: Something went wrong”
A message generates that says, “Something went wrong with processing the default view and we have restored the filter to its original state”
Error logs say, “Integrity constraint violation: 1052 Column ‘created_at’ in where clause is ambiguous.”

Additional information

A bug was introduced in 2.4.5 when the OrderGridCollectionFilter plugin was added in December 2021 to convert the created_at field to UTC. The plugin’s aroundAddFieldToFilter filter returns the field without calling $proceed. This means that created_at never gets processed through \Magento\Framework\Data\Collection\AbstractDb::addFieldToFilter, which in turns prevents created_at from getting mapped to main_table.

It’s also worth pointing out that the new plugin duplicates code which operates on created_at in \Magento\Sales\Model\ResourceModel\Order\Grid\Collection::addFieldToFilter, leaving everything in that collection’s if statement unreachable.

Release note

No response

Triage and priority

Severity: S0 - Affects critical data or functionality and leaves users without workaround.
Severity: S1 - Affects critical data or functionality and forces users to employ a workaround.
Severity: S2 - Affects non-critical data or functionality and forces users to employ a workaround.
Severity: S3 - Affects non-critical data or functionality and does not force users to employ a workaround.
Severity: S4 - Affects aesthetics, professional look and feel, “quality” or “usability”.

About this issue

Original URL
State: closed
Created 2 years ago
Reactions: 1
Comments: 15 (5 by maintainers)

Most upvoted comments

Hi @nwcasebolt,

I was also getting the error related to ‘created_at’ column when trying to apply “purchase date” filter on order grid. I have created below patch to fix this issue. I was not unable to upload with ‘.patch’ extension. So, the ‘.txt’ extension can be changed to ‘.patch’. Then the patch can be directly applied to Magento setup.

Patch-Fixed-CreatedAt-Filter-Issue-At-OrderGrid.txt

Thanks,

mohitnavik on Dec 16, 2022

Despite the name of the plugin referencing the order collection, the plugin is on a more generic class, so it can affect just about any UI admin grid that joins a table with “created_at”. On my installation, I experienced this error on the customer grid (because we customized it to join the B2B company table) as well as a third party module table that was joining the quote table.

I fixed it by patching \Magento\Sales\Plugin\Model\ResourceModel\Order\OrderGridCollectionFilter and removing these lines:

$fieldName = $subject->getConnection()->quoteIdentifier($field);
$condition = $subject->getConnection()->prepareSqlCondition($fieldName, $condition);
$subject->getSelect()->where($condition, null, Select::TYPE_CONDITION);

return $subject;

That allows it to call through to $proceed and execute all of the normal code.

zaximus84 on Dec 14, 2022

index 995bb83..3765a1d 100644
--- a/Plugin/Model/ResourceModel/Order/OrderGridCollectionFilter.php
+++ b/Plugin/Model/ResourceModel/Order/OrderGridCollectionFilter.php
@@ -52,7 +52,7 @@ class OrderGridCollectionFilter
                 }
             }

-            $fieldName = $subject->getConnection()->quoteIdentifier($field);
+            $fieldName = $subject->getConnection()->quoteIdentifier($field === 'created_at' ? 'main_table.created_at' : $field);
             $condition = $subject->getConnection()->prepareSqlCondition($fieldName, $condition);
             $subject->getSelect()->where($condition, null, Select::TYPE_CONDITION);

Here is a quick patch for getting it to work with any custom made that joins another tables that contains created_at column. Works on 2.4.5-p4.

mtytula on Jan 11, 2024

Hi @nwcasebolt,

Thank you for reporting and collaboration. Verified the issue on Magento 2.4-develop instance and the issue is reproducible. KIndly refer the screenshots.

Steps to reproduce

1.Add an afterSearch plugin on Magento\Framework\View\Element\UiComponent\DataProvider\Reporting that joins the sales_order table to the sales_order_grid table. Example:

$salesOrderTable = $collection->getConnection()->getTableName('sales_order');
            $collection->getSelect()->joinLeft(
                ['sales' => $salesOrderTable],
                'sales.increment_id = main_table.increment_id',
                'your_field'
            );

2.Filter the Order grid on purchase date.

We are getting a message “Something went wrong with processing the default view and we have restored the filter to its original state”

Hence Confirming the issue.

Thanks.

engcom-Bravo on Jan 16, 2023

I also found it an hour ago. It’s impossible that people that make such mistakes are working for a corporation like Adobe.

MyroslavDobra on Nov 9, 2022