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?