8

I need "Price Low to High" and "Price High to Low" sorting.

For this I have followed some guides which are referring to override Magento\Catalog\Block\Product\ProductList\Toolbar

These solutions are only applicable for older version of Magento 2 (Which doesn't use elasticsearch)

For 2.4+ versions where elastic search is mandatory, this guide wont work.

I have also tried this solution: https://devhooks.in/blog/how-to-add-custom-sorting-in-category-of-magento2

After applying above solution, I have checked query log, and query is like this:

SELECT `e`.*, `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`, IFNULL(review_summary.reviews_count, 0) AS `reviews_count`, IFNULL(review_summary.rating_summary, 0) AS `rating_summary`, `stock_status_index`.`stock_status` AS `is_salable` FROM `catalog_product_entity` AS `e`
 INNER JOIN `catalog_product_index_price` AS `price_index` ON price_index.entity_id = e.entity_id AND price_index.customer_group_id = 0 AND price_index.website_id = '1'
 LEFT JOIN `review_entity_summary` AS `review_summary` ON e.entity_id = review_summary.entity_pk_value AND review_summary.store_id = 1 AND review_summary.entity_type = (SELECT `review_entity`.`entity_id` FROM `review_entity` WHERE (entity_code = 'product'))
 INNER JOIN `cataloginventory_stock_status` AS `stock_status_index` ON e.entity_id = stock_status_index.product_id 
 WHERE (e.entity_id IN (1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, 21, 22, 23, 24, 25, 26, 27, 28, 29, 30, 31, 32)) 
 AND (stock_status_index.stock_status = 1) 
 ORDER BY FIELD(e.entity_id,1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30,31,32),
 `minimal_price` asc

Which is clearly showing that Elastic search is applying sorting before toolbar and before collection.

I just need to sort by minimal_price with asc and desc order, for low to high and high to low

4
  • Can you share code. Jan 19, 2023 at 10:38
  • My code is similar to the code available in link. You can see the link. I have added query log in my question for better understanding Jan 20, 2023 at 5:14
  • If minimal_price is a product attribute, you can modify the attribute, and under Storefront Properties, change "Used for Sorting in Product Listing" to Yes. Most themes have something on the frontend to set the sorting direction
    – Hunter
    Jan 25, 2023 at 18:44
  • @Hunter please check query in question, Magento calculates minimal_price during query execution, this is not attribute. If it was that easy there would be no need of custom sorting Jan 26, 2023 at 3:16

2 Answers 2

0

I once faced the same issue long back. Didn't remember how I exactly fixed it. But what I remember is I've tried to debug by checking the query being generated and it was some issue related to the reviews table being joined and some other Order by fields being added so I tried to do the same with your query and removed Left Join review_entity_summary line and removed additional Order by.

So the query becomes like this:

SELECT `e`.*, `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`, `stock_status_index`.`stock_status` AS `is_salable` FROM `catalog_product_entity` AS `e`
 INNER JOIN `catalog_product_index_price` AS `price_index` ON price_index.entity_id = e.entity_id AND price_index.customer_group_id = 0 AND price_index.website_id = '1'
 INNER JOIN `cataloginventory_stock_status` AS `stock_status_index` ON e.entity_id = stock_status_index.product_id 
 WHERE (e.entity_id IN (1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, 21, 22, 23, 24, 25, 26, 27, 28, 29, 30, 31, 32)) 
 AND (stock_status_index.stock_status = 1) ORDER BY `minimal_price` asc

Also, could you try the below and see the query?

$collection->setOrder('price ', 'asc');
0

Any updates? Does anyone find any working solution to implement custom sort order in Magento 2.4.5?

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.