magento2: M2.2.6 : "Order by price" not working in product listing

I’m running into the issue on a Magento 2.2.6 installation the “order by price” option for a product listing is not working. Neither ascending or descending.

Preconditions

  1. M2.2.6

Steps to reproduce

I’m not sure if it is part of the conditions to reproduce the problem but:

  1. Some products have special prices with a from- and to-date or only a from-date, but most of them are regular prices.
  2. Products have fixed product taxes.

Expected result

As a customer, If I order a product listing on price, the products should be ordered on price.

Actual result

They aren’t

Own research

If I look at the query that is eventually created by \Magento\Catalog\Block\Product\ListProduct::initializeProductCollection(), I get the following result:

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`, 
       `stock_status_index`.`stock_status` AS `is_salable` 
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 = '26' 
       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 = '1' 
       LEFT JOIN `cataloginventory_stock_status` AS `stock_status_index` 
              ON e.entity_id = stock_status_index.product_id 
                 AND stock_status_index.website_id = 0 
                 AND stock_status_index.stock_id = 1 
ORDER  BY `price_index`.`min_price` ASC, 
          `e`.`entity_id` DESC 
LIMIT  12 

The main thing I notice here is that it gets ordered by price_index.min_price. But if I look at my price index, min_price is set to 0 everywhere, except for the products that have a special price set.

This caused me to look at the catalog_product_price_index-table where I saw the same: almost all products have a min_price of 0, except the products that have special price set.

A bin/magento indexer:reindex or a re-save of the product does not change the price index.

So the problem might be the product listing (should it perhaps use a different column from the price index?) or should the price index have a min_price of at least the default price for regular products?

About this issue

  • Original URL
  • State: closed
  • Created 6 years ago
  • Comments: 16 (10 by maintainers)

Most upvoted comments

We also have the same issue with M2.3.1

I would not create a new issue for the same problem so will re-open this one. This issue relates to bundle price indexer as well and as I see still exists in M2.2.8 https://github.com/magento/magento2/blob/2.2/app/code/Magento/Bundle/Model/ResourceModel/Indexer/Price.php#L319 It affects category product sorting if there are bundle products. PR: https://github.com/magento/magento2/pull/23056

Hi @kanduvisla. Thank you for your report. The issue has been fixed in magento/magento2#18737 by @kanduvisla in 2.2-develop branch Related commit(s):

The fix will be available with the upcoming 2.2.8 release.

I found the core of the problem: It’s a mixup between AND and OR-statements in the building of the SQL-query. And even better: I also found that this has been fixed in the upcoming 2.3-branch:

https://github.com/magento/magento2/commit/14ab8ace12f0e95a476675548d0712d5ecaf9a26#diff-5a30a751305f50e9d7bbd8a2576a2b46

I’ll backport this issue to the 2.2-dev branch as well and send a PR.