Layered navigation block module attribute products count

[prestamin]

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.

Edited September 29, 2013 by prestamin (see edit history)

[mozack]

Hi,

 

Please try the mod i posted here: http://izak.me/ka

 

Thanks

 

Mozack

[prestamin]

Hi,

 

thank you but i tested this mod on Prestashop 1.5.4.1 and Layered navigation block 1.8.9 and it is not working correctly.

 

EDIT:

 

THIS WORKS!

 

I forgot to rebuild attribute index in admin for layered navigation.

 

THANK YOU MOZACK

Edited September 30, 2013 by prestamin (see edit history)

[mozack]

You're welcome...

 

Thanks

 

Mozack

[cedrickb]

hello,

I have the same problem, can you share the file again please?

 

Thanks in advance!