Negative price filter in layered navigation

Question

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

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...

Help please!!

Answer 1

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;

Answer 2

The solution documented here works for me, although I didn't test it thoroughly:

Rewrite the following file using the correct class rewrite procedure, and perform an price reindex subsequently.

File: vendor/magento/module-configurable-product/Model/ResourceModel/Product/Indexer/Price/Configurable.php

Method: _applyConfigurableOption()

Replace:

$select->columns(
    [
        'min_price' => new \Zend_Db_Expr('i.min_price - i.orig_price + io.min_price'),
        'max_price' => new \Zend_Db_Expr('i.max_price - i.orig_price + io.max_price'),
        'tier_price' => 'io.tier_price',
    ]
);

With:

$select->columns(
    [
        'min_price' => new \Zend_Db_Expr('io.min_price'),
        'max_price' => new \Zend_Db_Expr('io.max_price'),
        'tier_price' => 'io.tier_price'
    ]
);

Answer 3

For Magento 2.2.x

This is not a code but a data issue. In most cases it can appears when you use direct (used SQL queries) product import tools. I've taken one configurable product with negative min_price and max_price, imported with special prices and dropped special price for it and it's child products. After re-index I haven't seen any results, and only after saving this configurable product from admin, I saw the changes. It shows, that direct special prices import doesn't trig some important index processes. It still incorrect:

but it shows a bug. E.g. I took another product without any special prices for itself and its' child products, and set it special price for some option. After re-index everything looked fine:

So, check that products that you import don't have special price (at least configurable products), or change your import tool.

Answer 4

If you already tried with SQL query by @verheesj but still after reindex not working, you can then give following try, But before that plz take a DB backup.

UPDATE
catalog_product_index_price AS `DESTINATIONE`,
(
    SELECT
        *
    FROM
        `catalog_product_index_price`
    WHERE
            `min_price` < 0) AS `SOURCE`

    SET
        `DESTINATIONE`.`min_price` = `SOURCE`.`price`,
        `DESTINATIONE`.`max_price` = `SOURCE`.`price`,
        `DESTINATIONE`.`final_price` = `SOURCE`.`price`

    WHERE
            `DESTINATIONE`.`entity_id` = `SOURCE`.`entity_id`;

It will update all product price to it's main price