Magento 1.9 - Product collection sort by price not working fine

Question

This is my product collection.

$collectionWithRank = Mage::getResourceModel('catalog/product_collection')
    ->addCategoryFilter($cat_data)
    ->addAttributeToSelect('*')
    ->addAttributeToFilter('sort', ['neq' => ''])
    ->addAttributeToSort('sort', 'asc')
    ->addAttributeToSort('entity_id','desc');
    $collectionWithRank->load();

$collectionWithoutRank = Mage::getResourceModel('catalog/product_collection')
    ->addCategoryFilter($cat_data)
    // ->addAttributeToSelect(array('name', 'price', 'image', 'collect', 'url_key', 'sort'))
    ->addAttributeToSelect('*')
    ->addAttributeToFilter('sort', ['null' => true])
    ->addAttributeToSort('created_at', 'desc')
    ->addAttributeToSort('entity_id','desc');
    $collectionWithoutRank->load();

$merged_ids = array_merge($collectionWithRankids, $collectionWithoutRankids);
$collection = Mage::getResourceModel('catalog/product_collection')
    ->addFieldToFilter('entity_id', array('in' => $merged_ids))
    ->addAttributeToSelect('*');
$collection->getSelect()->order(new Zend_Db_Expr('FIELD(e.entity_id, ' . implode(',', $merged_ids).')'));

if($sortby = "price_h_to_l"){
    $collection->getSelect()->reset(Zend_Db_Select::ORDER);
    $collection->addAttributeToSort('price', 'desc');
}

Above code affecting the product collection, where the price sort results are as below.

• Product 1 - $7000
• Product 2 - $6800
• Product 3 - $5000
• Product 4 - $200
• Product 5 - $4800

The order by is not as expected.

Can anyone help to sort out this?.

Answer 1

Your code is working correctly as your SQL statement shows. addAttributeToSort('price', 'desc') is setting the sort order for min_price on the table catalog_product_index_price.

If you want to sort by another price you may use the following code (instead of the addAttributeToSort('price', 'desc') line):

//sort for example by final price (that's the frontend price before applying specific price rules)
//you can try to add like this also 'price' 
$collection->addPriceData();
$collection->getSelect()->order("final_price desc"); //or "price_index.final_price desc" if you get an error without the table alias

I hope that helps