Jump to content

Layered navigation block module attribute products count


Recommended Posts



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 by prestamin (see edit history)
Link to comment
Share on other sites

  • 2 weeks later...
  • 3 months later...

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
  • Create New...