Magento 2 Grid UIComponent – Include Table Alias in Column Filter

magento2uicomponent

I created a grid using an uiComponent xml file, and for the dataProvider I provided a collection which 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 JOIN'ed 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 solution 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?

Best Answer

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

Related Topic