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.


$skus = "24-MB04,24-MB01";
            'sku', array('in' => $skus)
$collection->getSelect()->order(new /Zend_Db_Expr("FIELD(e.sku, $skus)"));


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?

2 Answers 2


After a research, finally find the solution, we need process the $skus string to fix the syntax error in database query.

$skus = "'" . str_replace(",", "','", $skus) . "'";
$collection->getSelect()->order(new \Zend_Db_Expr("FIELD(sku,$skus)"));

Reference: Add quotes to values in a string separated by a comma php


Try this:

$collection->addAttributeToFilter('sku', array('eq' => $sku));

Also if you want to select SKU's that are looks like your:

$collection->addAttributeToFilter('sku', array('like' => '%'.$sku.'%'));
  • ASC or DESC order is not my need, what I need is sort order by custom skus string, you could see the example I given,
    – Key Shang
    Sep 1, 2017 at 9:13
  • Sorry for misunderstanding, try this with your collection. I've edited above answer.
    – Obscuro
    Sep 1, 2017 at 9:15
  • You still mistake my question, the problem is not the ->addAttributeToFilter, the problem is at the $collection->getSelect()->order(new /Zend_Db_Expr("FIELD(e.sku, $skus)"));, you could see the more detailed codes I given :)
    – Key Shang
    Sep 1, 2017 at 9:23
  • Oh sorry... I just can't get what do you want as a result.
    – Obscuro
    Sep 1, 2017 at 9:27
  • As I see you're trying to filter by column named as your SKU's, but there are, for sure, no columns named like that: "ORDER BY FIELD(sku, 24-MB04,24-MB01)"
    – Obscuro
    Sep 1, 2017 at 9:31

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.