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
- M2.2.6
Steps to reproduce
I’m not sure if it is part of the conditions to reproduce the problem but:
- Some products have special prices with a from- and to-date or only a from-date, but most of them are regular prices.
- 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)
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
ANDandOR-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.