Magento 1.9 – Override Product Collection Sort Order

catalogcollection;magento-1.9productsorting

I'm using Magento 1 and inside catalog/product/list.phtml I use the following code to add a custom sort order product_on_top:

<?php
$_productCollection=$this->getLoadedProductCollection();
$_helper = $this->helper('catalog/output');

$_productCollection->clear()
                    ->getSelect()
                    ->joinLeft(array('cpei'=>'catalog_product_entity_int'), 'e.entity_id = cpei.entity_id AND cpei.attribute_id = 197 AND cpei.store_id = 0', 'cpei.value as product_on_top' )
                    ->order( array( 'product_on_top DESC' ) );

By default Magento adds the price_index.min_price asc and final_price asc to the sort order and my product_on_top desc is added add the end of the order sort.

I would like to know if it is possible to make my custom product_on_top attribute the first attribute to sort on and not the price fields ?

Best Answer

Implement this answer only when you know the existing list of sort orders applied on the collection, as the below solution will reset the list of all existing sort orders on the collection and reapply them in the needful order.

Used reset(Zend_Db_Select::ORDER) to reset all the existing sortorders on the collection. 

<?php
$_productCollection=$this->getLoadedProductCollection();
$_helper = $this->helper('catalog/output');

$_productCollection->clear()
                    ->getSelect()
                    ->reset(Zend_Db_Select::ORDER)
                    ->joinLeft(array('cpei'=>'catalog_product_entity_int'), 'e.entity_id = cpei.entity_id AND cpei.attribute_id = 197 AND cpei.store_id = 0', 'cpei.value as product_on_top' )
                    ->order(array('product_on_top DESC', 'price_index.min_price ASC', 'final_price ASC'));
Related Topic