How to specify custom sort order for product collection?

by

The question:

I am trying to create a product collection based on array of products ids, and also sort the collection based on the ids array.

$productIds = array(318,310,311);
$collection = Mage::getModel('catalog/product')
        ->getCollection()
        ->setOrder('entity_id', 'asc') // This will not do the job
        ->addAttributeToSelect('*')
        ->addAttributeToFilter('status', 1)
        ->addAttributeToFilter('entity_id', array(
    'in' => $productIds,
        ));

I want to sort collected as they appear in $productIds array that is 318, 310, 311 but above code will return collection sort like 310,311, 312.

Is this possible without using plain MySQL query as given below?

SELECT *
FROM catalog_product_entity
WHERE entity_id IN (318,
                    310,
                    311)
ORDER BY FIELD(entity_id, 318, 310, 311);

The Solutions:

Below are the methods you can try. The first solution is probably the best. Try others if the first one doesn’t work. Senior developers aren’t just copying/pasting – they read the methods carefully & apply them wisely to each case.

Method 1

Sadly Magento will validate the order options in Varien_Data_Collection_Db _setOrder function. But you can get the select of the collection and add a new expression to build the order as you like.

/* @var $collection Mage_Catalog_Model_Resource_Product_Collection */
$collection = Mage::getModel('catalog/product')
    ->getCollection()
    ->addAttributeToSelect('*')
    ->addAttributeToFilter('status', 1)
    ->addAttributeToFilter('entity_id', array(
            'in' => $productIds,
        ));

$collection->getSelect()->order(new Zend_Db_Expr('FIELD(e.entity_id, ' . implode(',', $productIds).')'));

foreach($collection as $product) {
    var_dump($product->getId());
}

Here you should see that the product ids are in the order of the array.


All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Leave a Comment