I have searched along the web for the solution and I have not found yet. Let me give you the context a little.
I’m using Magento 2.3.6, where we got around 57800 products. When you go to the category page and try to sort the products by price, it does not work properly.
After digging a little, I got the SQL that is run and had some tests done. Here is what I found:
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` FROM `catalog_product_entity` AS `e` INNER JOIN `catalog_category_product_index_store1` 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=31 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' ORDER BY price_index.min_price = 0, price_index.min_price desc, price_index.min_price = 0, price_index.min_price ASC
This SQL command is responsible to get the products collection ordered by price. You can se that it has an AND to get the category where the products should be part of
cat_index.category_id=31. If I run this SQL, it returns 0 rows. Even though the category has all the products attached to it.
But if you change the
cat_index.category_id=31 by the default category `cat_index.category_id=2, it actually returns the ordered products. See bellow both results:
Can anyone help to find what is actually happening? Thanks!