Database Query To Retrieve Manufacturers For A Particular Category Including Its Childrens

[Maheshmohan1093]

Hi,

 

This is the query to get the manufacturer's list with manufacturers selected to their particular category and their childrens.

 

SELECT m.* FROM ps_manufacturer m
            INNER JOIN (SELECT DISTINCT p.id_manufacturer FROM ps_product p
            inner join ps_category_product pc on (pc.id_product = p.id_product)
            inner join ps_category c on (c.id_category = pc.id_category and c.id_parent=' .$id .')) fm ON (fm.id_manufacturer =     m.id_manufacturer) WHERE m.`active` = 1 ORDER BY m.`name` LIMIT 5

 

where $id is the category id.

 

But I would like to retrieve the manufacturers of a particular category including its sub-childrens as well.

 

Kindly help.

 

 

[doekia]

I should first emphasis that this query could be a real performance killer depending on your catalog.

 

The query with count been added / does the same as distinct but more usefull (however count product multiple times if present on multiple cat/subcat).

select
	count(p.id_manufacturer), m.name
from (
	select cp.id_product
	from ps_category_product cp
	inner join (
		select c.id_category
		from ps_category _c
		inner join ps_category c on c.nleft between _c.nleft and _c.nright
		where _c.id_category = @id_category
	) __c on __c.id_category = cp.id_category
) _p
inner join ps_product_shop ps on ps.id_product = _p.id_product and ps.active = 1 and ps.id_shop = @id_shop
inner join ps_product p on p.id_product = ps.id_product
inner join ps_manufacturer m on m.id_manufacturer = p.id_manufacturer and m.active = 1
group by m.id_manufacturer

The EXPLAIN plan on a 1500 categories tree containing 300 products

id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	PRIMARY	<derived2>	ALL	NULL	NULL	NULL	NULL	1281	Using temporary; Using filesort
1	PRIMARY	p	eq_ref	PRIMARY,product_manufacturer	PRIMARY	4	_p.id_product	1	 
1	PRIMARY	ps	eq_ref	PRIMARY	PRIMARY	8	presta16.p.id_product,const	1	Using where
1	PRIMARY	m	eq_ref	PRIMARY	PRIMARY	4	presta16.p.id_manufacturer	1	Using where
2	DERIVED	<derived3>	ALL	NULL	NULL	NULL	NULL	126	 
2	DERIVED	cp	ref	PRIMARY,id_category	id_category	4	__c.id_category	2	Using index
3	DERIVED	_c	const	PRIMARY,nleftrightactive,nright	PRIMARY	4	 	1	 
3	DERIVED	c	range	nleftrightactive	nleftrightactive	4	NULL	126	Using where; Using index

The result

count(p.id_manufacturer)	name
205	Kanlux
18	Mean Well
14	Philips
19	Xanlite
15	OSRAM
129	Easy Connect
2	3M
20	HIPOW
1	V-TAC