Magento sort by price: sort by final price instead of price:

I want to sort by price filter to sort by final price of product instead of just price. For product I have price 10 but special price is 100 , so when user sort product by price, sorting looks wrong.

Please help me to fix it.


3 Answers 3


The Background:

As @Shawn Abramson mentioned, this is not possible without an additional mechanism.

The sorting must be specified in the first query (Mage_Eav_Model_Entity_Collection_Abstract:1045), as this is the result set that future queries add data to. I believe you need to always include a separate join to the price index table. This is unfortunate, as it is more records to search/load, reducing performance. That trade-off is up to you and your client.

The Mechanism:

$products = Mage::getResourceSingleton('catalog/product_collection');

    ['price_table' => Mage::getResourceSingleton('core/resource')->getTable('catalog/product_index_price')],
    'entity_id = entity_id',
    [ 'price', 'final_price' ],
        'customer_group_id' => 0,
        'website_id' => Mage::app()->getStore()->getWebsiteId()
$products->getSelect()->order('price_table.final_price DESC');

The Explanation:

The first four lines are whatever your standard collection filtering code is. Thereafter there are two functions:

  1. $products->joinTable(): this function joins the catalog/product_index_price table and retrieve the two columns for reference. Please Note: you need specify the customer_group_id and the website_id unless these placeholder values work for you.
  2. $products->getSelect()->order(): this function performs the order process.
$arrProductIds is Array of product ids.

$productsCollection = Mage::getModel('catalog/product')->getCollection()
                ->addAttributeToFilter('entity_id', array('in' => $arrProductIds))
                ->addAttributeToSort('price', 'DESC')

        foreach ($productsCollection as $product) {
            echo $product->getname();
            echo $product->getfinalprice().'  ';

Final price is not an actual attribute so some special logic would need to be created. You can, however, sort by special_price attribute.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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