Récupérer la liste de toute les caractéristiques de certains produits

[dojbouli]

Bonjour,

 

Voilà, j'utilise actuellement un Leo Thème.

Sur ce dernier il y a un module Meilleurs ventes.

 

J'aimerai le personnaliser pour faire en sorte que la liste des caractéristiques de mes produits soient affichés dans ce module.

 

Or j'ai un problème quant à la requête qu'il faut exécuter pour afficher toutes les caractéristiques d'un produit.

 

Voici la requête que j'ai pour le moment, elle ne retourne qu'une caractéristique.

 

SELECT DISTINCT p.id_product, p.*, product_shop.*, stock.out_of_stock, IFNULL(stock.quantity, 0) as quantity, product_attribute_shop.`id_product_attribute`, product_attribute_shop.minimal_quantity AS product_attribute_minimal_quantity, pl.`description`, pl.`description_short`, pl.`available_now`, pl.`available_later`, pl.`link_rewrite`, pl.`meta_description`, pl.`meta_keywords`, pl.`meta_title`, pl.`name`, image_shop.`id_image`, il.`legend`, m.`name` AS manufacturer_name, cl.`name` AS category_default, ps.`quantity` AS sales, DATEDIFF(product_shop.`date_add`, DATE_SUB(NOW(), INTERVAL 20 DAY)) > 0 AS new, product_shop.price AS orderprice,fpl.name as namefeature1,fvk.value as namefeature1

FROM `ps_category_product` cp

LEFT JOIN `ps_product` p ON p.`id_product` = cp.`id_product`

INNER JOIN ps_product_shop product_shop ON (product_shop.id_product = p.id_product AND product_shop.id_shop = 1)

LEFT JOIN `ps_product_attribute` pa ON (p.`id_product` = pa.`id_product`)

LEFT JOIN ps_product_attribute_shop product_attribute_shop ON (product_attribute_shop.id_product_attribute = pa.id_product_attribute AND product_attribute_shop.id_shop = 1 AND product_attribute_shop.`default_on` = 1)

LEFT JOIN ps_stock_available stock ON (stock.id_product = p.id_product AND stock.id_product_attribute = IFNULL(`product_attribute_shop`.id_product_attribute, 0) AND stock.id_shop = 1 )

LEFT JOIN `ps_category_lang` cl ON (product_shop.`id_category_default` = cl.`id_category` AND cl.`id_lang` = 2 AND cl.id_shop = 1 )

LEFT JOIN `ps_product_lang` pl ON (p.`id_product` = pl.`id_product` AND pl.`id_lang` = 2 AND pl.id_shop = 1 )

LEFT JOIN `ps_image` i ON (i.`id_product` = p.`id_product`) LEFT JOIN ps_image_shop image_shop ON (image_shop.id_image = i.id_image AND image_shop.id_shop = 1 AND image_shop.cover=1)

LEFT JOIN `ps_image_lang` il ON (image_shop.`id_image` = il.`id_image` AND il.`id_lang` = 2) LEFT JOIN `ps_manufacturer` m ON m.`id_manufacturer` = p.`id_manufacturer`

 

// -------------- Récupération Feature ID 9 -------------------- //

 

LEFT JOIN `ps_feature_product` fp ON fp.`id_product` = p.`id_product` AND fp.`id_feature`=9

LEFT JOIN `ps_feature_lang` fpl ON fpl.`id_feature` = fp.`id_feature` AND fpl.`id_feature`=9

LEFT JOIN `ps_feature_value_lang` fvk ON fvk.`id_feature_value` = fp.`id_feature_value`

 

// -------------- ---------------------------------- -------------------- //

 

// ------------- Solution qui bug... Très long et plantage --------------//

LEFT JOIN `ps_feature_product` fp2 ON fp2.`id_product` = p.`id_product` AND fp2.`id_feature`=8

LEFT JOIN `ps_feature_lang` fpl2 ON fpl2.`id_feature` = fp2.`id_feature` AND fpl2.`id_feature`=8

LEFT JOIN `ps_feature_value_lang` fvk2 ON fvk2.`id_feature_value` = fp2.`id_feature_value`

 

LEFT JOIN `ps_feature_product` fp3 ON fp3.`id_product` = p.`id_product` AND fp3.`id_feature`=7

LEFT JOIN `ps_feature_lang` fpl3 ON fpl3.`id_feature` = fp3.`id_feature` AND fpl3.`id_feature`=7

LEFT JOIN `ps_feature_value_lang` fvk3 ON fvk3.`id_feature_value` = fp3.`id_feature_value`

// ---------------------------------------------------//

 

INNER JOIN `ps_product_sale` ps ON (ps.`id_product` = cp.`id_product`)

WHERE product_shop.`id_shop` = 1 AND (pa.id_product_attribute IS NULL OR product_attribute_shop.id_shop=1) AND (i.id_image IS NULL OR image_shop.id_shop=1) AND product_shop.`active` = 1 AND product_shop.`visibility` IN ("both", "catalog") ORDER BY sales DESC LIMIT 0,8

 

Merci d'avance si vous avez la solution.

Edited August 8, 2013 by dojbouli (see edit history)