Magento - Collection Filter by Array Keep Order

Question

Is it possible to filter a Magento collection using an array of id's BUT have the collection results ordered by the order of the id's passed to the filter.

For example:

$collection = Mage::getModel('catalog/product')
                  ->getCollection()
                  ->addAttributeToFilter('entity_id', array(
                       'in' => array(1, 3, 2),
                   ));

I would like the collection to have products in order, 1,3,2 so as when looping through the collection they come out in that specific order?

The only alternative i currently have is to manually create an array of products:

$productIds = array(1,3,2);
$collection = array();

foreach($productIds as $productId) {
    $collection[] = Mage::getModel('catalog/product')->load($productId);
}

This obviously works but seems like an ugly way to do this.

is there a way to do this purely via magento collections?

Drew Hunter · Answer 1 · 2012-07-27T07:37:40.430

$productIds = array(1,3,2);

/**
 * Build up a case statement to ensure the order of ids is preserved
 */
$orderString = array('CASE e.entity_id');
foreach($productIds as $i => $productId) {
    $orderString[] = 'WHEN '.$productId.' THEN '.$i;
}
$orderString[] = 'END';
$orderString = implode(' ', $orderString);

/**
 * Filter the collection
 */
$productCollection = Mage::getModel('catalog/product')->getCollection()
    ->addAttributeToFilter('entity_id', array('in' => $productIds));

/**
 * Apply the order based on the case statement
 */
$productCollection->getSelect()
    ->order(new Zend_Db_Expr($orderString))

Just perfect solution. Works great. – Muttley75 Apr 10 '15 at 22:23 — Muttley75, Apr 10 '15 at 22:23

score 3 · Answer 2 · edited May 23 '17 at 12:34

3

Pretty old but a simple solution I found on stackoverflow is

$productIds = array(1,3,2);
$products = Mage::getModel('catalog/product')->getCollection()
            ->addAttributeToFilter('entity_id', array('in' => $productIds));
$products->getSelect()->order("find_in_set(entity_id,'".implode(',',$productIds)."')");

from here on stackoverflow

edited May 23 '17 at 12:34

Community

1
1

answered Mar 29 '17 at 15:21

Haris

605
1
10
23

score 1 · Answer 3 · answered Jul 18 '12 at 07:44

You can load the collection before sorting it in PHP. Eg :

$result = array();
$productIds = array(1,3,2);
$collection = Mage::getModel('catalog/product')
    ->getCollection()
    ->addAttributeToFilter('entity_id', array('in' => $productIds))
    ->load();

foreach ($productIds as $productId) {
    if ($product = $collection->getItemById($productId)) {
        $result[$productId] = $product;
    }
}

Else, purely with collections, you should first pass by the Zend_Db_Select object of the collection, to be able to sort on expressions (what may not be possible with eg EAV based collections and calls to addAttributeToSort or sortOrder).
Then you can either use multiple order calls as stated in Gershon's answer, or use a single order with a generated CASE WHEN THEN statement. Knowing that it could depend on the maximum number of IDs you may have to filter on.

Gershon Herczeg · Answer 4 · 2012-07-17T21:55:15.567

0

This is a challenging question, here is a solution that should work:

$collection = Mage::getModel('catalog/product')
                  ->getCollection()
                  ->addAttributeToFilter('entity_id', array(
                       'in' => array(1928, 1930, 1929),
                   ))
           ->addAttributeToSort('entity_id = 1928', 'ASC')
           ->addAttributeToSort('entity_id = 1930', 'ASC')
           ->addAttributeToSort('entity_id = 1929', 'ASC')
           ;

edited Jul 17 '12 at 21:55

answered Jul 17 '12 at 21:39

Gershon Herczeg

3,016
23
33

Hi Gershon, thanks for the answer. It makes sense with regards to the attributeToSort but the problem is that addAttributeToFilter example above is just an example, there could be an arbitrary number of elements in the array. I guess i could loop over each element and call addAttributeToSort but thats then getting in to the territory where i started with the loop to create the collection as an array – Jul 17 '12 at 22:02

Magento - Collection Filter by Array Keep Order

4 Answers4

Linked