cancel
Showing results for 
Search instead for 
Did you mean: 

configurable products - price filter not working correctly

configurable products - price filter not working correctly

Hi all,

 

I have been testing price filters with configurable products. 

I found out that it looks like price filter works just for the minimum price of the configurable product.

 

When debugging I found it that magento stores all the prices here: catalog_product_index_price

with configurable products it will also fill out min_price and max_price field - based on the prices defined in associated products.

Now I have one configurable product with prices: 11€,22€,33€

in the table I see min_price 11€ and max_price 33€

 

which is correct.

I have filter ranges

0-20€

20-40€

 

 

the problem is that when using filters, configurable product will be shown just in range 0-20€, despite the fact that to it are connected simple products with prices 22€ and 33€ and they should be shown also in that ranges.

 

I have debugged SQL that selects the product in ranges:

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`,
IF(at_name.value_id > 0,
at_name.value,
at_name_default.value) AS `name`
FROM
`catalog_product_entity` AS `e`
INNER JOIN
`catalog_category_product_index` AS `cat_index` ON cat_index.product_id = e.entity_id
AND cat_index.store_id = 5
AND cat_index.visibility IN (2 , 4)
AND cat_index.category_id = '383'
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 = 0
LEFT JOIN
`catalog_product_entity_varchar` AS `at_name_default` ON (`at_name_default`.`entity_id` = `e`.`entity_id`)
AND (`at_name_default`.`attribute_id` = '71')
AND `at_name_default`.`store_id` = 0
LEFT JOIN
`catalog_product_entity_varchar` AS `at_name` ON (`at_name`.`entity_id` = `e`.`entity_id`)
AND (`at_name`.`attribute_id` = '71')
AND (`at_name`.`store_id` = 5)
WHERE
((price_index.min_price - (price_index.min_price / (1 + (CASE price_index.tax_class_id
WHEN 2 THEN 0.2200
ELSE 0
END)) * CASE price_index.tax_class_id
WHEN 2 THEN 0.2200
ELSE 0
END) + ((price_index.min_price - (price_index.min_price / (1 + (CASE price_index.tax_class_id
WHEN 2 THEN 0.2200
ELSE 0
END)) * CASE price_index.tax_class_id
WHEN 2 THEN 0.2200
ELSE 0
END)) * CASE price_index.tax_class_id
WHEN 2 THEN 0.2200
ELSE 0
END)) >= 19.995000)
AND ((price_index.min_price - (price_index.min_price / (1 + (CASE price_index.tax_class_id
WHEN 2 THEN 0.2200
ELSE 0
END)) * CASE price_index.tax_class_id
WHEN 2 THEN 0.2200
ELSE 0
END) + ((price_index.min_price - (price_index.min_price / (1 + (CASE price_index.tax_class_id
WHEN 2 THEN 0.2200
ELSE 0
END)) * CASE price_index.tax_class_id
WHEN 2 THEN 0.2200
ELSE 0
END)) * CASE price_index.tax_class_id
WHEN 2 THEN 0.2200
ELSE 0
END)) < 40.005000)
ORDER BY `name` ASC
LIMIT 32;

 

 

and I see that SQL is weird written. I have bolded parts where I think it holds error.

So in SQL we have two conditions

min_price >= 19.995

min_price <= 40.005

 

but to be honest, correct would have to be

max_price >= 19.995

min_price <= 40.005

 

because in my example this would mean that 33€  (max_price) product price is higher then 19.995 and lower then 40.005€

and the default SQL checks minimum price 11€ if it is greater then 19.995 which is of course not true.

 

Is this default magento behavior or did my installation got messed up?

3 REPLIES

Re: configurable products - price filter not working correctly

is there really no1 with that kind of problem?

 

I think this is a big problem when filtering prices since products are not displayed correctly?

Re: configurable products - price filter not working correctly

do you still have that problem ?

 

try 0-0 range ?

Re: configurable products - price filter not working correctly

we do have the exact same problem - how did you solve it?