12

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);
18

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.

  • It works! Thank you. Can we combine sort by SKU to above? – Tahir Yasin Oct 28 '13 at 10:28
  • You can add multiple fields to this order by the syntax FIELD(x, 1, 2, 3), FIELD(y, 3, 2, 1) – David Manners Oct 28 '13 at 10:39

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.