I'm working on category page, and try to sort the products collection by Special Price.
I've override setCollection() of \Magento\Catalog\Block\Product\ProductList\Toolbar enter image description here

And the result is the only sort for Normal price (not including Special Price): enter image description here

So my solution is Left Join with catalog_product_entity_decimal table (this table is using for store Special Price, also store Normal Price as well, 149.0000 is normal price, and 120.0000 is special price). enter image description here enter image description here

And I print the query, and got this:

SELECT `e`.*, `cat_index`.`position` AS `cat_index_position`, `price_index`.`price`, `price_index`.`tax_class_id`, `price_index`.`final_price`, IF(price_index.tier_price IS NOT NULL, LEAST(price_index.min_price, price_index.tier_price), price_index.min_price) AS `minimal_price`, `price_index`.`min_price`, `price_index`.`max_price`, `price_index`.`tier_price`, `catalog_product_entity_decimal`.`value` AS `special_price` FROM `catalog_product_entity` AS `e`
 INNER JOIN `catalog_category_product_index` AS `cat_index` ON cat_index.product_id=e.entity_id AND cat_index.store_id=1 AND cat_index.visibility IN(2, 4) AND cat_index.category_id='127'
 INNER JOIN `catalog_product_index_price` AS `price_index` ON price_index.entity_id = e.entity_id AND price_index.website_id = '1' AND price_index.customer_group_id = 0
 LEFT JOIN `catalog_product_entity_decimal` ON e.entity_id = catalog_product_entity_decimal.entity_id WHERE (attribute_id = 78 or attribute_id = 77) AND (catalog_product_entity_decimal.store_id = 0) GROUP BY `entity_id` ORDER BY `special_price` desc, `price` desc

But with this, the GROUP BY affected before ORDER BY, and ruin my result.
Its remove the 120.0000 and only show the 149.0000 in query result

If I remove the GROUP BY, it will show both, but the Magento code will error with "Already exists Entity Id".

If there is any way to make GROUP BY go after ORDER BY? Or better way to solve the problems?

  • I think there is no need for coding. You can add sort by special price in frontend by simply changing Used for Sorting in Product Listing value to Yes in special_price attribute. Commented Jun 24, 2019 at 11:10
  • @MohitKumarArora i've tried as you said, but its not working, i've clear cache as well.
    – fudu
    Commented Jun 24, 2019 at 11:25
  • Hello @fudu did you found any solution for this? Commented Oct 7, 2021 at 3:17
  • @IsuruDilshan sorry, i don't find anything yet.
    – fudu
    Commented Oct 7, 2021 at 8:41
  • Hello @fudu I have tested this in 2.4.1 and this->_collection->setOrder('price', 'desc'); is working as expected. But on the listing page, it shows final_price instead of minimal_price. For the sorting, they use a minimal price. This is the reason why the front end got confused. So I create a plugin for Magento\Catalog\Model\Product and return MinimalPrice for afterGetSpecialPrice. That shows the correct value in the front end. Thanks for your help. Commented Oct 8, 2021 at 3:30


Your Answer

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

Browse other questions tagged or ask your own question.