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 see 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 below both results:

category_id=31 category_id=31

category_id=2 category_id=2

May anyone help me to find what is actually happening? Thanks!


On the Admin sidebar, go to Stores > Settings > Configuration.

In the left panel, expand Catalog and choose Catalog underneath.

Expand the Storefront section and do the following:

Set Use Flat Catalog Category to Yes. (If necessary, deselect the Use system value checkbox.) enter image description here Set Use Flat Catalog Product to Yes.

After that you have to run

bin/magento indexer:reindex

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.