8

I added custom column to 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 grid as expected. But when I try to sort by this column or filter - an error occurre

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

How can I fix this?

UPDATE

Now I try resolve this problem by deleting CustomerName class (and remove reference to it in column tag in xml) and instead I added _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)

12

There is a factory method which 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 an other collection related factory method to use expressions in 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');
  • Thanks a lot! Was struggling with this for half a day. Where did you find/learn this? Surely, not in the docs? – Jānis Elmeris Nov 28 '17 at 12:29
  • as the original post mentioned: had to add custom column to admin grid - with filter and sort options. Cost about 1.5 days to figure it out (ofc with least footprint) – Mammouth Dec 5 '17 at 18:39
  • I'm trying to display the both invoice and sale order increment_ids on my custom module admin grid with sale order increment id is relabeled as 'ordincrementid'. Still even i had used the addFilterToMap(); i'm unable to filter the sale order increment_id. It returning this error => Column 'increment_id' in where clause is ambiguous – Ask Bytes Sep 2 at 12:07
6

It took some time, but I figured this out

In my xml listing:

<column name="firstname">
        <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 Vendor\Module\Model\ResourceModel\SomeCustomModel\Grid:

/**
 * adding email to customer name column
 */
protected function _initSelect()
{
    parent::_initSelect();
    $this->getSelect()->joinLeft(
        ['ce' => $this->getTable('customer_entity')],
        'main_table.customer_id = ce.entity_id',
        ['*']
    );

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

In Vendor\Module\Model\ResourceModel\SomeCustomModel

/**
 * addding ability to filter by column with customer name and email
 */
protected function _renderFiltersBefore()
{
    $wherePart = $this->getSelect()->getPart(\Magento\Framework\DB\Select::WHERE);
    foreach ($wherePart as $key => $cond) {
        $wherePart[$key] = str_replace('`firstname`', 'CONCAT(firstname," <",email,">")', $cond);
    }
    $this->getSelect()->setPart(\Magento\Framework\DB\Select::WHERE, $wherePart);
    parent::_renderFiltersBefore();
}

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.