Magento – Negative price filter in layered navigation

layered-navigationprice

I'm experiencing a strange issue here where the price navigation filter (layered navigation) somehow came up with negative price ranges.

negative price range

If I flush the price tables it seems to solve the problem, until next time prices get re-indexed. Then the negative price ranges re-appear.

Another issue is that the price ranges are not even accurate. It would show the following: CA$0.00 – CA$9.99 (2) – as if there are 2 items within this price range. But we do not have any items priced within this price range…

inaccurate price range

Help please!!

Best Answer

Configurable products have redundant prices that cause wrong configurable min_price calculation.

To see the count of all configurable products prices records from the database:

SELECT count(*) FROM catalog_product_entity_decimal WHERE entity_id in (SELECT entity_id FROM catalog_product_entity c where c.type_id = 'configurable');

Please backup your database before executing the query below!!!

To resolve, delete the price records from the database:

DELETE FROM catalog_product_entity_decimal WHERE entity_id in (SELECT entity_id FROM catalog_product_entity c where c.type_id = 'configurable');

Then run the following commands which will update the catalog_product_index_price table:

bin/mage index:reindex bin/mage cache:clean

Run the following to check for negative product prices:

select * from catalog_product_index_price where min_price < 0;

Related Topic