6

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?

20

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

  • thanks for your above code, i was stuck for this and it really saved my time. – bhargav shastri Nov 28 '18 at 11:16

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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