3

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 ...

5

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 '17 at 7:08
  • @ArthurSereno I dont know whats the issue for using dash. you can raise new question for same. – Rakesh Jesadiya Sep 1 '17 at 8:33
  • Thanks your advice, raise a new question here now magento.stackexchange.com/questions/191568/… – Key Shang Sep 1 '17 at 9:08
  • ok., some one may know give answer for you. – Rakesh Jesadiya Sep 1 '17 at 9:08
  • Not work for me. I have SKU with spacing. @Rakesh – Dhaduk Mitesh Apr 13 '18 at 11:13
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.

  • Sorting using id. Is this work for you? – Dhaduk Mitesh Apr 13 '18 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 '18 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 – Dhaduk Mitesh Apr 16 '18 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 '18 at 7:13
  • This is not work for me. – Dhaduk Mitesh Apr 16 '18 at 7:24

Your Answer

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

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