Product collection sort order by skus could use $collection->getSelect()->order(new /Zend_Db_Expr("FIELD(e.sku, $skus)"));
(Magento 2: Order product collection by the order string(skus, ids etc) given, not DESC or ASC), but when sku has a hyphen in it, it will result a error like below.
Example:
$skus = "24-MB04,24-MB01";
$collection->addAttributeToSelect('*')
->addAttributeToFilter(
'sku', array('in' => $skus)
);
$collection->getSelect()->order(new /Zend_Db_Expr("FIELD(e.sku, $skus)"));
Result:
Error filtering template: SQLSTATE[42S22]: Column not found: 1054 Unknown column 'MB04' in 'order clause', query was: SELECT `e`.*, `stock_status_index`.`stock_status` AS `is_salable` FROM `catalog_product_entity` AS `e` 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 WHERE (`e`.`sku` IN('24-MB04', '24-MB01')) ORDER BY FIELD(sku, 24-MB04,24-MB01)
Obviously, the hyphen(-) in the sku(24-MB04) result the Unknown column 'MB04' in 'order clause' error, because when the sku doesn't have hyphen(-) in it, the codes work well, so how to fix the problem?
Best Answer
After a research, finally find the solution, we need process the $skus string to fix the syntax error in database query.
Reference: Add quotes to values in a string separated by a comma php