Hi,
i use Layered navigation block module. My products have size atribute and in select filter on options there is a number of products for example for size "large" ( SizeXXL (3) ).
Problem is that counter (nbr) counts products with quantity 0 also and it doesn't filter products with zero quantity.
How can i change query below that it counts only products that have quantity > 0 in ps_product_attribute table?
SELECT COUNT(DISTINCT p.id_product) nbr, lpa.id_attribute_group,
a.color, al.name attribute_name, agl.public_name attribute_group_name , lpa.id_attribute, ag.is_color_group,
liagl.url_name name_url_name, liagl.meta_title name_meta_title, lial.url_name value_url_name, lial.meta_title value_meta_title
FROM ps_layered_product_attribute lpa
INNER JOIN ps_attribute a
ON a.id_attribute = lpa.id_attribute
INNER JOIN ps_attribute_lang al
ON al.id_attribute = a.id_attribute
AND al.id_lang = 1
INNER JOIN ps_product as p
ON p.id_product = lpa.id_product
INNER JOIN ps_attribute_group ag
ON ag.id_attribute_group = lpa.id_attribute_group
INNER JOIN ps_attribute_group_lang agl
ON agl.id_attribute_group = lpa.id_attribute_group
AND agl.id_lang = 1
LEFT JOIN ps_layered_indexable_attribute_group_lang_value liagl
ON (liagl.id_attribute_group = lpa.id_attribute_group AND liagl.id_lang = 1)
LEFT JOIN ps_layered_indexable_attribute_lang_value lial
ON (lial.id_attribute = lpa.id_attribute AND lial.id_lang = 1)
WHERE a.id_attribute_group = 6 AND lpa.`id_shop` = 1 AND p.id_product IN (
SELECT id_product
FROM ps_category_product cp
INNER JOIN ps_category c ON (c.id_category = cp.id_category AND
c.nleft >= 3
AND c.nright <= 34
AND c.active = 1))
GROUP BY lpa.id_attribute
ORDER BY id_attribute_group, a.position
Thank you.