Query per elenco prodotti e percorso categorie

[nicola10]

Ciao a tutti,

sto provando una query che mi permetta di ottenere per ciascun record l'elenco dei prodotti con le relative categorie e sottocategorie cui appartiene ciascun prodotto (categoria-->sottocategoria-->sottocategoria1-->..-->prodotto,

ma non funziona correttamente: la query restituisce più record con lo stesso prodotto con una combinazione diversa di categorie.

 

Ciascun prodotto l'ho associato ad una sola categoria e la versione di prestashop è la 1.4.8.2

 

select
ps_category_product.id_category, ps_category_product.id_product,
root.id_category as cat1, down1.id_category as cat2, down2.id_category as cat3,
down3.id_category as cat4, down4.id_category as cat5, down5.id_category as cat6,
down6.id_category as cat7, down7.id_category as cat8, ps_product.id_product, ps_product.id_category_default
 from (ps_category as root
left outer
 join ps_category as down1
on down1.id_parent = root.id_category
left outer
 join ps_category as down2
on down2.id_parent = down1.id_category
left outer
 join ps_category as down3
on down3.id_parent = down2.id_category
left outer
 join ps_category as down4
on down4.id_parent = down3.id_category
left outer
 join ps_category as down5
on down5.id_parent = down4.id_category
left outer
 join ps_category as down6
on down6.id_parent = down5.id_category
left outer
 join ps_category as down7
on down7.id_parent = down6.id_category
left outer
 join ps_category as down8
on down8.id_parent = down7.id_category
right join  
ps_category_product on root.id_category	= ps_category_product.id_category)
left join
ps_product on ps_category_product.id_product = ps_product.id_product
group by ps_category_product.id_product

[nicola10]

Ho rifatto la query precedente così:

 

SELECT cl.id_category,
cl.name,
cl2.id_category,
cl2.name AS parent_name,
cl.description,
cl.link_rewrite,
cl.id_lang,
ps_product.id_product,
ps_product_lang.name,
ps_product_lang.link_rewrite,
ps_product.id_category_default
FROM ps_category c
INNER JOIN ps_category_lang cl ON cl.id_category = c.id_category
INNER JOIN ps_category_lang cl2 ON cl2.id_category = c.id_parent
LEFT JOIN ps_category_product ON cl.id_category = ps_category_product.id_category
RIGHT JOIN ps_product ON ps_category_product.id_product = ps_product.id_product
inner JOIN ps_product_lang ON ps_product.id_product = ps_product_lang.id_product
AND cl2.id_lang = cl.id_lang
WHERE cl.id_lang =5
group by ps_product.id_product

 

Però il risultato contiene solo le 2 categorie più vicine al prodotto.

 

Nel mio caso i livelli di categoria sono 8

Edited June 5, 2012 by nicola10 (see edit history)

[nicola10]

Ho combinato le 2 query insieme:

 

SELECT distinct
ps_category_product.id_category, ps_category_product.id_product,
root.id_category as cat1, down1.id_category as cat2, down2.id_category as cat3,
down3.id_category as cat4, down4.id_category as cat5, down5.id_category as cat6,
down6.id_category as cat7, down7.id_category as cat8,
ps_product.id_product,
ps_product_lang.name,
ps_product_lang.link_rewrite,
ps_product.id_category_default
 from ps_category as root
left outer
 join ps_category as down1
   on down1.id_parent = root.id_category
left outer
 join ps_category as down2
   on down2.id_parent = down1.id_category
left outer
 join ps_category as down3
   on down3.id_parent = down2.id_category
left outer
 join ps_category as down4
   on down4.id_parent = down3.id_category
left outer
 join ps_category as down5
   on down5.id_parent = down4.id_category
left outer
 join ps_category as down6
   on down6.id_parent = down5.id_category
left outer
 join ps_category as down7
   on down7.id_parent = down6.id_category
left outer
 join ps_category as down8
   on down8.id_parent = down7.id_category
INNER JOIN ps_category_lang ON root.id_category = ps_category_lang.id_category    
LEFT JOIN ps_category_product ON root.id_category = ps_category_product.id_category
RIGHT JOIN ps_product ON ps_category_product.id_product = ps_product.id_product
inner JOIN ps_product_lang ON ps_product.id_product = ps_product_lang.id_product
AND ps_category_lang.id_lang = 5

 

Però il numero di record restituiti è circa il doppio di quelli attesi