Magento – Custom Sort Order For Collection

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.

After some research I got following seems to work:

The setOrder() method accepts an array.

 $collection = Mage::getModel('catalog/product')->getCollection();
 $collection->addAttributeToFilter('status', 1); //enabled
 $collection->addAttributeToFilter('visibility', 4); //catalog, search
 $collection->addAttributeToSelect('name');

 **$collection->setOrder('name', 'desc');
 $collection->setOrder('sku', 'asc');**

/* 
  this way you can set order for two different field
*/
 $products = $collection->load();
 //with the use of this you can print your query.
 $products->printLogQuery(true);

 echo "<pre>";
 foreach($products AS $_product)
 {   
    echo $_product->getName()."<br />";
 }

I have test in my machine and I got this result

SELECT e.*, IF(at_status.value_id > 0, at_status.value, at_status_default.value) AS status, IF(at_visibility.value_id > 0, at_visibility.value, at_visibility_default.value) AS visibility, IF(at_name.value_id > 0, at_name.value, at_name_default.value) AS name, price_index.price, price_index.tax_class_id, price_index.final_price, IF(price_index.tier_price IS NOT NULL, LEAST(price_index.min_price, price_index.tier_price), price_index.min_price) AS minimal_price, price_index.min_price, price_index.max_price, price_index.tier_price FROM catalog_product_entity AS e INNER JOIN catalog_product_entity_int AS at_status_default ON (at_status_default.entity_id = e.entity_id) AND (at_status_default.attribute_id = '273') AND at_status_default.store_id = 0 LEFT JOIN catalog_product_entity_int AS at_status ON (at_status.entity_id = e.entity_id) AND (at_status.attribute_id = '273') AND (at_status.store_id = 1) INNER JOIN catalog_product_entity_int AS at_visibility_default ON (at_visibility_default.entity_id = e.entity_id) AND (at_visibility_default.attribute_id = '526') AND at_visibility_default.store_id = 0 LEFT JOIN catalog_product_entity_int AS at_visibility ON (at_visibility.entity_id = e.entity_id) AND (at_visibility.attribute_id = '526') AND (at_visibility.store_id = 1) LEFT JOIN catalog_product_entity_varchar AS at_name_default ON (at_name_default.entity_id = e.entity_id) AND (at_name_default.attribute_id = '96') AND at_name_default.store_id = 0 LEFT JOIN catalog_product_entity_varchar AS at_name ON (at_name.entity_id = e.entity_id) AND (at_name.attribute_id = '96') AND (at_name.store_id = 1) INNER JOIN catalog_product_index_price AS price_index ON price_index.entity_id = e.entity_id AND price_index.website_id = '1' AND price_index.customer_group_id = 0 WHERE (IF(at_status.value_id > 0, at_status.value, at_status_default.value) = '1') AND (IF(at_visibility.value_id > 0, at_visibility.value, at_visibility_default.value) = '4') ORDER BY name DESC, e.sku ASC