3

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

enter image description here

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

3

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

0

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');
  • same error unknown column "fullname" – Idham Choudry Aug 23 '18 at 8:34
  • @IdhamChoudry why you have used addFilterToMap i dont't getting this? without $this->addFilterToMap('customer_name', 'Fullname'); use can get your result and if this will not work we have easy way is renderer in grid – Rutvee Sojitra Aug 23 '18 at 8:38

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.