For example, I want collection list in order "sku2,sku3,sku1", it is not simple DESC or ASC order. How to achieve it?

Example codes is below:

$skus = 'sku2,sku3,sku1';
        'sku', array('in' => $skus)

Magento 1.x has a way here How to specify custom sort order for product collection?, but it doesn't work on Magento 2:

$collection->getSelect()->order(new Zend_Db_Expr("FIELD(e.sku, $skus)"));

Fatal error: Uncaught Error: Class '.../Zend_Db_Expr' not found in ...

3 Answers 3


You can do in magento 2 as below way, use \Zend_Db_Expr

$collection->getSelect()->order(new \Zend_Db_Expr('FIELD(e.entity_id, ' . implode(',', $skus).')'));
  • Up vote. Zend_Db_Expr is working now. But when sku has a dash in it, it will result a error like Unknown column 'MB04' in 'order clause', the example sku is "24-MB04", how to fix it?
    – Key Shang
    Sep 1, 2017 at 7:08
  • @ArthurSereno I dont know whats the issue for using dash. you can raise new question for same. Sep 1, 2017 at 8:33
  • Thanks your advice, raise a new question here now magento.stackexchange.com/questions/191568/…
    – Key Shang
    Sep 1, 2017 at 9:08
  • ok., some one may know give answer for you. Sep 1, 2017 at 9:08
  • Not work for me. I have SKU with spacing. @Rakesh Apr 13, 2018 at 11:13

Number Type (like ids etc.)

$ids = '4,5,3,1,2';
$collection = ...; // get the collection
$collection->getSelect()->order(new \Zend_Db_Expr("FIELD(entity_id,$ids)"));

entity_id is the name of column which you want to sort, you need change it when in different collection, like page_id, block_id, banner_id etc.

String Type (like skus etc.)

$ids = 'sku4,sku5,sku3,sku1,sku2';
$collection = ...; // get the collection
$skus = "'" . str_replace(",", "','", $skus) . "'"; //need process string
$collection->getSelect()->order(new \Zend_Db_Expr("FIELD(sku,$skus)"));

Number type use the solution of "String Type" is working too, but I think It is not appropriate.

  • Sorting using id. Is this work for you? Apr 13, 2018 at 11:22
  • @DhadukMitesh It works for me. Notice product use the entity_id, you could see my first code sample.
    – Key Shang
    Apr 14, 2018 at 6:27
  • I tried but not work for me. When category page load then I set custom price to product and I required to sort that custom price(asc/desc). @Key Apr 16, 2018 at 6:36
  • @DhadukMitesh You should make sure your collection have the 'custom price' data in it. And order price you could just use codes like $collection->setOrder('price', 'DESC')
    – Key Shang
    Apr 16, 2018 at 7:13
  • This is not work for me. Apr 16, 2018 at 7:24
$this->_collection->getSelect()->order("FIND_IN_SET(e.sku,'" . implode(',',$skus) . "') DESC");

or better

$this->_collection->getSelect()->order(new \Zend_Db_Expr("find_in_set(e.sku,'" . implode(',',$skus) . "') DESC"));

for me it works but main condition is DESC because in other way this items will be missing

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.