Magenot 2.4.5 - Custom sorting not working with elasticsearch

Question

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

Answer 1

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');

Answer 2

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