I'm trying to sort Advanced Search result by Order parameter and Price.
Eg:
User sorts results by Manufacturer
, direction DESC
from toolbar. In this case I need to order by manufaturer DESC
then by price ASC
.
I have created an Observer that Observs catalog_block_product_list_collection
This is my module Observer.php function:
public function catalog_block_product_list_collection(Varien_Event_Observer $observer)
{
$collection = $observer->getEvent()->getCollection();
$collection->getSelect()->order('price','ASC');
/*OR $collection->addOrder('price',Varien_Data_Collection::SORT_ORDER_DESC);*/
return $this;
}
But result list is sorted by Manufacturer only.
If I print $collection->getSelect()->__toString();
in mynamespace/mymodule/catalog/product/list.phtml
i see that order is set correctly
ORDER BY `manufacturer` DESC, `price` ASC LIMIT 20
Query execution from MySQL returns the expeted result, Magento don't.
What's wrong with my code?
Is it possible to do what I'm trying to do ?
EDIT [SOLUTION]
After googling a while and a day of trying i solved my problem. Here is the code:
/**
* @param Varien_Event_Observer $observer
* @return Spm_Sorter_Model_Observer
*/
public function catalog_block_product_list_collection(Varien_Event_Observer $observer)
{
// Get && Clear Collection
$collection = $observer->getEvent()->getCollection();
$collection->clear();
// get current Sort && Direction
$currSortOrder = Mage::getBlockSingleton('catalog/product_list_toolbar')->getCurrentOrder();
$currSortDirection = Mage::getBlockSingleton('catalog/product_list_toolbar')->getCurrentDirection();
// If is current Sort is price || special price need some special stuff
if(in_array($currSortOrder,['price','special_price'])){
// Rest collection sort
$collection->getSelect()->reset(Zend_Db_Select::ORDER);
/**
* Set Special Price order Index.
* Discounted products must be at the top of the list
* This 'index' is needed because of :
* ORDER BY special_price -> products with no discount comes first, they have NULL value
* If special price is out of date range we will have wrong order to,
* special price is valued but not valid.
* ORDER BY min_price -> products with no discount but price lower than discounted ones comes first
* */
$collection->getSelect()->columns(
array(
'special_price_index' => new Zend_Db_Expr('IF(`price_index`.`price` <> `price_index`.`min_price`,`price_index`.`price`, 99999)')
)
);
// Set special price sort. Direction ASC if current order != pecial_price, current otherwise
$collection->getSelect()->order('special_price_index',($currSortOrder === 'special_price' ? $currSortDirection : Varien_Data_Collection::SORT_ORDER_ASC));
}
// Set price sort. Direction ASC if current order != price, current otherwise
$collection->setOrder('price',($currSortOrder === 'price' ? $currSortDirection : Varien_Data_Collection::SORT_ORDER_ASC));
return $this;
}
$collection
is loaded somewhere before my observer.$collection->clear();
solves the problem.