0

I need to use ORDER BY CASE name which is product name

Query:

SELECT `e`.*, `cat_index`.`position` AS `cat_index_position`, 
`stock_status_index`.`stock_status` AS `is_salable` 
FROM `catalog_product_entity` AS `e`
INNER JOIN `catalog_category_product_index` AS `cat_index` 
ON cat_index.product_id=e.entity_id AND cat_index.store_id=1 
AND cat_index.category_id='3'
LEFT JOIN `cataloginventory_stock_status` AS `stock_status_index` 
ON e.entity_id = stock_status_index.product_id AND
stock_status_index.website_id = 0 AND stock_status_index.stock_id = 1 
ORDER BY CASE WHEN `name` LIKE '%banana%' THEN 1 
WHEN `name` LIKE '%apple%' THEN 2 
ELSE 3 END

... $collection->addCategoryFilter($category) $collection->getSelect()->order(new \Zend_Db_Expr("CASE WHENnameLIKE '%banana%' THEN 1 WHENnameLIKE '%apple%' THEN 2 ELSE 3 END")); ...

Exception #1 (PDOException): SQLSTATE[42S22]: Column not found: 1054 Unknown column 'name' in 'order clause'

If do setOrder instead of order then it Works but it just order alphabetically like so:

$collection->addCategoryFilter($category)->setOrder('name', 'ASC');

I need Expression what is going wrong?

  • because of name has not alias of table name so you need to specify [alias].name in setOrder(). – Hardik Visa Jun 22 at 6:48
  • @HardikVisa SELECT e.*, cat_index.position` AS cat_index_position, stock_status_index.stock_status AS is_salable FROM catalog_product_entity AS e INNER JOIN catalog_category_product_index AS cat_index ON cat_index.product_id=e.entity_id AND cat_index.store_id=1 AND cat_index.category_id='3' LEFT JOIN cataloginventory_stock_status AS stock_status_index ON e.entity_id = stock_status_index.product_id AND stock_status_index.website_id = 0 AND stock_status_index.stock_id = 1 ORDER BY CASE WHEN e.name LIKE '%banana%' THEN 1 WHEN e.name LIKE '%apple%' THEN 2 ELSE 3 END` – Juliano Vargas Jun 24 at 8:38
  • @HardikVisa Doesn't work Exception #1 (PDOException): SQLSTATE[42S22]: Column not found: 1054 Unknown column 'e.name' in 'order clause' – Juliano Vargas Jun 24 at 8:38

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.