Sort by 2 attributes with different orders

Question

I have to catalog sort by 2 attributes with different orders (asc, desc). I have change in function setCollection($collection). But here order is overwrite on another order.

Here is my code, which I have changed.

public function setCollection($collection)
    {
        $this->_collection = $collection;

        $this->_collection->setCurPage($this->getCurrentPage());

        // we need to set pagination only if passed value integer and more that 0
        $limit = (int)$this->getLimit();
        if ($limit) {
            $this->_collection->setPageSize($limit);
        }
        if ($this->getCurrentOrder()) {

            # Code Added By Prashant Parekh : Sort By Pairing Idenity : 31/12/2013
            if($this->getCurrentOrder()=='name'){
                $orders = array('desc'=>'pairing_identity','asc'=>'name');
                foreach($orders as $key=>$value){
                    if($value=='name')
                        $this->_collection->setOrder($value, $this->getCurrentDirection());
                    else
                        $this->_collection->setOrder($value, $key);
                }
            } else {
                $this->_collection->setOrder($this->getCurrentOrder(), $this->getCurrentDirection());
            }
        }
        return $this;
    }

its set default order DESC, whenever first time product list is load. I want pairing_identity => 'desc' and name => 'asc'. But this code set pairing_identity => 'desc' and name => 'desc'.

Answer 1

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