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?
