0

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;
}
  • this issue occurs because after your statement another statement running for fetch record in Magento. So only way is try to use backtrace and any other collection running after your statement – Rama Chandran M Jun 27 '17 at 17:15
  • @RamaChandran Thank you for your reply. I think that the problem is that $collection is loaded somewhere before my observer. $collection->clear(); solves the problem. – vils Jun 29 '17 at 7:06
  • Great News :) . – Rama Chandran M Jun 29 '17 at 7:13
  • i will provide in ans. please accept. so this ticket is consider as resolved. – Rama Chandran M Jun 29 '17 at 19:19
0

This issue occurs because after your statement another statement running for fetch record in Magento.

The problem is that $collection is loaded somewhere before our observer. $collection->clear(); clear collection

Thanks

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.