9

I created a grid using an uiComponent XML file, and for the dataProvider, I provided a collection that is using JOINs with some additional tables.

Because of that, I'm getting the following error while trying to filter the grid by the email field:

Exception #0 (Zend_Db_Statement_Exception): SQLSTATE[23000]: Integrity constraint violation: 1052 Column 'email' in where clause is ambiguous

which is correct. The email field is present both in the main table and in one of the joined tables.

So my question is: how can I make the email column filter use the main_table.email instead of just email in the query? I tried to search for solutions in core uiComponents, but none of them seem to have such ambiguous columns (along with a solution for that).

My column definition looks like this:

<column name="email">
    <argument name="data" xsi:type="array">
        <item name="config" xsi:type="array">
            <item name="filter" xsi:type="string">text</item>
            <item name="sorting" xsi:type="string">asc</item>
            <item name="label" xsi:type="string" translate="true">Email</item>
        </item>
    </argument>
</column>

I also tried putting this code in the <filters> tag to check if it helps:

<filterInput name="email">
    <argument name="data" xsi:type="array">
        <item name="config" xsi:type="array">
            <item name="dataScope" xsi:type="string">main_table.email</item>
            <item name="label" xsi:type="string" translate="true">Email</item>
        </item>
    </argument>
</filterInput>

But it turns out it doesn't. What should I do to achieve the desired effect, apart from changing the collection query itself?

2

1 Answer 1

31

You can force using table alias in condition with addFilterToMap() method. Apply it in the _initSelect() method of your Collection class:

protected function _initSelect()
{
    $this->addFilterToMap('email', 'main_table.email');

    parent::_initSelect();
}

I found this answer here Conflicts in where clause with ambiguous column names

2
  • thanks for your above code, i was stuck for this and it really saved my time. Nov 28, 2018 at 11:16
  • Perfect answer!!! +1 made my day :) Nov 15, 2021 at 9:34

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.