Magento 2: Custom sort order for product collection(skus, ids etc)

Question

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';
$collection->addAttributeToSelect('*')
    ->addAttributeToFilter(
        '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 ...

Answer 1

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).')'));

Answer 2

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.

Answer 3

$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