Magento – Magento 2 – custom admin grid field – error when sorting or filtering

admincustomgridmagento2uicomponent

I added a custom column to the admin grid, like this

<column name="customer_name" class="Vendor\Module\Ui\Component\Listing\Columns\CustomerName">
        <argument name="data" xsi:type="array">
            <item name="config" xsi:type="array">
                <item name="filter" xsi:type="string">text</item>
                <item name="editor" xsi:type="string">text</item>
                <item name="sortable" xsi:type="string">true</item>
                <item name="label" xsi:type="string" translate="true">Customer Name</item>
                <item name="sortOrder" xsi:type="number">30</item>
            </item>
        </argument>
    </column>

In my CustomerName class I create values for this column:

public function prepareDataSource(array $dataSource)
{
    $fieldName = $this->getData('name');
    foreach ($dataSource['data']['items'] as & $item) {
        $customer = $this->customerRepository->getById($item['customer_id']);
        $name = $customer ? $customer->getFirstName().' <'.$customer->getEmail().'>' : '';
        $item[$fieldName] = $name;
    }
    return $dataSource;
}

It shows in the grid as expected. But when I try to sort by this column or filter – an error occurred

SQLSTATE[42S22]: Column not found: 1054 Unknown column 'customer_name' in 'order clause'

How can I fix this?

UPDATE

Now I try to resolve this problem by deleting CustomerName class (and remove reference to it in column tag in XML) and instead I added the _renderFiltersBefore() function in my collection class

 protected function _renderFiltersBefore() {
     $joinTable = $this->getTable('customer_entity');
     $this->getSelect()->join($joinTable.' as customer_entity','main_table.customer_id = customer_entity.entity_id', array('*'));
     $this->getSelect()->columns('CONCAT(firstname," <",email,">") as customer_name');
     parent::_renderFiltersBefore();
}

Now sorting is working, but filtering does not (get the same error)

Best Answer

There is a factory method that you can use, and this is the addFilterToMap(). Where Magento 2 rendering filter just replace the subjects of the conditions based on the mapped fields

you can call it either in _initSelect or _renderFiltersBefore method.

for a simple column which already existing in the selection (good to resolve ambiguous errors)

$this
     ->addFilterToMap('customer_id', 'ce.entity_id');

but in your case need to map an expression as

$this
    ->addFilterToMap(
       'customer_name ', 
       new \Zend_Db_Expr('CONCAT(ce.firstname," <",ce.email,">")')
    );

so the condition part of the query will be

... WHERE (CONCAT(ce.firstname," <",ce.email,">") LIKE '%@yippie.com%') ...
``` instead of

... WHERE (customer_name LIKE '%@yippie.com%') ...


also, you can use another collection related factory method to use expressions in the `SELECT` part of the query

    $this
        ->addExpressionFieldToSelect(
            'firstname',
            new \Zend_Db_Expr('CONCAT(ce.firstname," <",ce.email,">")'),
            []
        )

instead of 

    $this->getSelect()->columns('CONCAT(ce.firstname," <",ce.email,">") as firstname');
Related Topic