Error Filtering grid from joined table collection

Question

I successfully create a custom customer grid which use select join, the problem is i concat customer first name and customer last name as a new column alias, and when i filter the column it shows error

this is how i join the collection:

$this->getSelect()->join(
                ['secondTable' => $this->getTable('customer_entity_varchar')],
                'main_table.entity_id = secondTable.entity_id AND secondTable.attribute_id = 159 AND secondTable.value = 1',
                ['customer_name'=>'CONCAT(main_table.firstname," ",main_table.lastname)']
            );
$this->addFilterToMap('customer_name', 'CONCAT(main_table.firstname,main_table.lastname)');

this is my grid column:

  <column name="customer_name">
        <argument name="data" xsi:type="array">
            <item name="config" xsi:type="array">
                <item name="filter" xsi:type="string">text</item>
                <item name="bodyTmpl" xsi:type="string">ui/grid/cells/html</item>
                <item name="label" translate="true" xsi:type="string">Name</item>
            </item>
        </argument>
    </column>

var/log/execption.log

[2018-08-23 15:06:25] main.CRITICAL: SQLSTATE[42S22]: Column not found: 1054 Unknown column 'CONCAT(main_table.firstname,main_table.lastname)' in 'where clause', query was: SELECT COUNT(*) FROM customer_entity AS main_table

Answer 1

you need to create a column like this after your join

$select->getSelect()->columns(new \Zend_Db_Expr('CONCAT_WS(" ", main_table.lastname, main_table.firstname) as customer_name'));
$this->addFilterToMap(
    'customer_name',
    new \Zend_Db_Expr('CONCAT_WS(" ", main_table.lastname, main_table.firstname)')
);

I hope this will help you

Answer 2

Your query shuold be like:

$this->getSelect()->join(
            ['secondTable' => $this->getTable('customer_entity_varchar')],
            'main_table.entity_id = secondTable.entity_id AND secondTable.attribute_id = 159 AND secondTable.value = 1',
            ['customer_name'=>'CONCAT(main_table.firstname," ",main_table.lastname) AS Fullname']
        );
$this->addFilterToMap('customer_name', 'Fullname');