1

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?.

  • can you add a Mage::log($collection->getSelect()->__toString()); right after $collection->addAttributeToSort('price', 'desc'); and check the sort order of the statement? – HelgeB Feb 22 at 13:56
  • I have reviewed the SQL statement where it's order by is "ORDER BY price_index.min_price desc". It's 'price' or 'min_price'?. – Ejilarasan J Feb 23 at 6:52
0

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

  • This is the issue I got on var/report. How to proceed further?. a:5:{i:0;s:1892:"SELECT e.* FROM catalog_product_entity AS e WHERE (e.entity_id IN(<<--ENTITY IDS-->>)) ORDER BY price DESC LIMIT 10 ";i:1;s:3335:"#0 /var/www/html/lib/Varien/Db/Statement/Pdo/Mysql.php(110): Zend_Db_Statement_Pdo->_execute(Array) #1 /var/www/html/app/code/core/Zend/Db/Statement.php(291): Varien_Db_Statement_Pdo_Mysql->_execute(Array) #2 /var/www/html/lib/Zend/Db/Adapter/Abstract.php(480): Zend_Db_Statement->execute(Array) – Ejilarasan J Feb 23 at 9:07
  • is that the output of $collection = Mage::getResourceModel('catalog/product_collection')->addFieldToFilter('entity_id', array('in' => $merged_ids))->addAttributeToSelect('*')->addPriceData(); $collection->getSelect()->order("price desc");? It looks like there are missing the price table joins which should be added by addPriceData() – HelgeB Feb 23 at 9:12
  • Now I don't get any issues, but the result is still the same. The price sort is not as expected. – Ejilarasan J Feb 23 at 9:59
  • Did you sort by final price? If yes aand the result is the same check the content of you price index table, the collection works on that and the sql should sort correctly. Compare the data from price index table with product price attribure. Maybe the price index is not up to date. There might be also some final price calculation based on catalog price rules in your data – HelgeB Feb 23 at 10:15
  • By the way: what is the price in your output? Which method do you call on the collection item? – HelgeB Feb 23 at 10:24

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.