0

I need a new "dynamic" column in my custom grid so I created grid, I extend \Magento\Framework\View\Element\UiComponent\DataProvider\SearchResult and I add in _beforeLoad() new column:

const NEW_COLUMN = "(SELECT COUNT(*) FROM my_table WHERE group_id=main_table.group_id)";

 $this->getSelect()
            ->columns(['new_column' => new \Zend_Db_Expr(self::NEW_COLUMN)])

Sorting by this column work perfectly, but when I try to use a filter I got an error:

Column not found: 1054 Unknown column 'new_column' in 'where clause'

Here are SQL queries:

Grid query:

SELECT `main_table`.*, (SELECT COUNT(*) FROM my_table WHERE a.group_id=main_table.group_id) AS `new_column` FROM `main_base_table` AS `main_table` WHERE (`type` = '0') LIMIT 20

Filter query:

SELECT COUNT(*) FROM `main_base_table` AS `main_table` WHERE (`new_column` >= '1') AND (`new_column` <= '3') AND (`type` = '0')
0

You need to pass table alias with group_id like below example

const NEW_COLUMN = "(SELECT COUNT(*) FROM my_table WHERE alias.group_id=main_table.group_id)";

alias = Your table alias

| improve this answer | |
  • Still the same. In error message, I see only main query. I believe there might be a problem Grid query: SELECT main_table.*, (SELECT COUNT(*) FROM my_table WHERE a.group_id=main_table.group_id) AS new_column FROM main_base_table AS main_table WHERE (type = '0') LIMIT 20 Filter query: SELECT COUNT(*) FROM main_base_table AS main_table WHERE (new_column >= '1') AND (new_column <= '3') AND (type = '0') – Maciej Sep 4 '17 at 14:58
  • You need to also add a.group_id in grid column layout. – Suresh Chikani Sep 5 '17 at 4:51

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.