I want to sort a catalog_product_collection by multiple attributes but with special input.

As I understand the method addAttributeToSort calling it like this


Seems to do the trick, if it can load the attribute. But I need to call it like that:

addAttributeToSort("attribute IN ('001')");

Which will fail because it cannot load an attribute called attribute IN ('001')

How would I sort the collection in this case?

Reason: We want the product collection order in a special format. I have the algorithm done and its working fine (pagionation is broken now, but thats another topic) but a co worker showed me that you could so an ORDER BY like that:

ORDER BY x.Component_Stone_Color IN ('001') DESC, x.Component_Stone_Color IN ('280') DESC, ....


Something like this seems to work:

$sql = $collection->getSelect(); $sql->order(array("sw_stone_color IN ('001') DESC", "sw_stone_color IN ('280') DESC", "sw_...", ...));

But the needed attributes are not (yet?) selected. An $collection->addAttributeToSelect(array('sw_stone_color', 'sw_effects', 'sw_foiling', 'sw_motifs')); doesnt seem to do the trick, sadly. (They are simply not selected when I look at the output query)

I'm in the catalog_product_collection_load_before event.

1 Answer 1


You have to use Zend_Db_Expr while using something different from a simple field name:

$collection->getSelect()->order(array(new Zend_Db_Expr('sw_stone_color IN ('001') DESC'), new Zend_Db_Expr(...)));
  • Looks like it doesnt matter, because its added to the SELECT nontheless. However, some attributes seem to be not SELECTED and therefore the query fails. What event would you hook up to when working with catalog product collections? catalog_block_product_list_collection doesnt not seem to be triggered for me. And "load_before" also dont have the needed attributes.
    – paskl
    Commented Feb 25, 2016 at 14:33
  • Try with catalog_product_collection_load_after instead of catalog_product_collection_load_before? Since you are adding a sort it should work. Commented Feb 25, 2016 at 14:35

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.