magento2: addFilterToMap function is not working for certain columns

Preconditions and environment

Magento 2.4.5-p1

There an issue related with addFilterToMap function on custom grid collection used in custom admin grid. If you map column with name created_at it will conflicts with same column in joined table in where condition. It seems that new plugin Magento\Sales\Plugin\Model\ResourceModel\Order\OrderGridCollectionFilter in aroundAddFieldToFilter function ignores AbstractDb’s mapper _getMappedField that called inside _translateCondition. In this case you get error “SQLSTATE[23000]: Integrity constraint violation: 1052 Column ‘created_at’ in where clause is ambiguous”.

Steps to reproduce

Create custom grid in admin based on table that has created_at column
Rewrite _initSelect to add join with another table that also has created_at column and after that apply filter map $this->addFilterToMap('created_at', 'main_table.created_at');
Apply filter on the grid by ‘created_at’ field or you can just apply addFieldToFilter by created_at column programmatically on you collection

Expected result

filtering of a collection by mapped column created_at works without errors and uses applied filter mapping

Actual result

applied filter mapping for created_at column not working. Getting errorSQLSTATE[23000]: Integrity constraint violation: 1052 Column 'created_at' in where clause is ambiguous.

Additional information

No response

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: open
Created a year ago
Comments: 29 (9 by maintainers)

Most upvoted comments

@dzhuser @engcom-Dash I have replicated the issue and a single line fix.

Find line:

$fieldName = $subject->getConnection()->quoteIdentifier($field);

Replace with:

$fieldName = $subject->getConnection()->quoteIdentifier('main_table.' . $field);

I will create a PR soon. I need to sync my forked repository before that.

ananth-iyer on Jan 18, 2023

Also if we do this the plugin could be refactored as a before plugin

fgerodez on Mar 27, 2023

Quite a few other grids depend on Magento\Framework\View\Element\UiComponent\DataProvider\SearchResult and I think we could tweak the plugin to have it call $proceed($field, $condition) all the time. I have to do further tests but I don’t see the point of manually adding the sql clause there instead of letting the parent (and its custom logic potentially) do the work in addFieldToFilter.

That would give us something like:

I’ve looked at most of the inherited classes of Magento\Framework\View\Element\UiComponent\DataProvider\SearchResult and the only problematic code I found is this one in Magento\Customer\Model\ResourceModel\Grid\Collection:

It duplicates the logic of the plugin. At the moment the code if ($field === 'created_at') is never called because the plugin doesn’t call $proceed($field, $condition) for created_at.

So by always calling $proceed($field, $condition) in the filter and removing this if block in Magento\Customer\Model\ResourceModel\Grid\Collection we can make sure that each concrete Grid Class has the ability to add fields to the query any way it wants without impacting the others.

fgerodez on Mar 27, 2023

@ananth-iyer thank you!

SerhiiMandrykin on Mar 3, 2023