Magento – Sort by 2 attributes with different orders

catalogmagento-1.7sorting

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'.

Best Answer

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

Works

   $collection = Mage::getResourceModel('catalog/product_collection');
    Mage::getSingleton('catalog/product_status')->addVisibleFilterToCollection($collection);
    Mage::getSingleton('catalog/product_visibility')->addVisibleInCatalogFilterToCollection($collection);

    $collection->addMinimalPrice()
        ->addFinalPrice()
        ->addTaxPercents()
        ->addAttributeToSelect(Mage::getSingleton('catalog/config')->getProductAttributes())
        ->addUrlRewrite()
        ->addStoreFilter()
        ->addAttributeToFilter('special_from_date', array('date' => true, 'to' => now()))
        ->addAttributeToFilter('special_to_date', array('or'=> array(
                0 => array('date' => true, 'from' => now()),
                1 => array('is' => new Zend_Db_Expr('null')))
                                                  ), 'left')
        ->addAttributeToSort('name')
        ->setPageSize(10)
        ->setCurPage(1)

    ;
    die((string)$collection->getSelect());

I have a ORDER BY clause. Did you refresh indexes and caches? I don't think this is the problem, but might be. I have no attribute flash here, so I just tried with name and this works.

Magento 1.8 – How to Sort Catalog Search by Two Attributes

First, create an extension (in my example it's called Emzee_Sorter) and listen to the event catalog_block_product_list_collection in your config.xml:

<?xml version="1.0"?>
<config>
    <modules>
        <Emzee_Sorter>
            <version>1.0.0</version>
        </Emzee_Sorter>
    </modules>
    <global>
        <models>
            <emzee_sorter>
                <class>Emzee_Sorter_Model</class>
            </emzee_sorter>
        </models>
        <events>
            <catalog_block_product_list_collection>
                <observers>
                    <emzee_sorter>
                        <class>emzee_sorter/observer</class>
                        <method>catalogBlockProductListCollection</method>
                    </emzee_sorter>
                </observers>
            </catalog_block_product_list_collection>
        </events>
    </global>
</config>

Then create the observer class:

<?php

class Emzee_Sorter_Model_Observer
{
    /**
     * @param Varien_Event_Observer $observer
     * @return Emzee_Sorter_Model_Observer
     */
    public function catalogBlockProductListCollection(Varien_Event_Observer $observer)
    {
        $collection = $observer->getEvent()->getCollection();
        $collection->addOrder('sort_2', Varien_Data_Collection::SORT_ORDER_DESC);
        return $this;
    }
}

sort_2 is the attribute containing 55H and so on. Make sure that you set Used in Product Listing to Yes for this attribute so that the attribute can be found when flat catalog is enabled.

This code will sort the products on search results and category pages. If you want to limit the functionality to search results, you can do something like this:

<?php

class Emzee_Sorter_Model_Observer
{
    /**
     * @param Varien_Event_Observer $observer
     * @return Emzee_Sorter_Model_Observer
     */
    public function catalogBlockProductListCollection(Varien_Event_Observer $observer)
    {
        $request = Mage::app()->getFrontController()->getRequest();
        $action = $request->getModuleName() . '_' . $request->getControllerName() . '_' . $request->getActionName();
        if ($action === 'catalogsearch_result_index' || $action === 'catalogsearch_advanced_result') {
            $collection = $observer->getEvent()->getCollection();
            $collection->addOrder('sort_2', Varien_Data_Collection::SORT_ORDER_DESC);
        }
        return $this;
    }
}

Best Answer

Related Solutions

Magento – How to sort special price collection

Works

Magento 1.8 – How to Sort Catalog Search by Two Attributes

Related Topic