1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94
| -- Retourne la liste des produits par catégorie--
SELECT
t.rate,
p.id_product,
MAX(image_shop.id_image) id_image,
IFNULL(MAX(product_attribute_shop.id_product_attribute),
0) id_product_attribute,
p.id_supplier,
p.id_manufacturer,
p.id_category_default,
p.id_tax_rules_group,
p.on_sale,
p.online_only,
p.price,
p.show_price,
sp.reduction,
sp.reduction_type,
stock.out_of_stock,
IFNULL(stock.quantity, 0) as quantity,
product_attribute_shop.minimal_quantity AS product_attribute_minimal_quantity,
pl.name produit,
pl.description_short,
pl.available_now,
pl.available_later,
m.name AS manufacturer_name,
cl.name AS category_default,
DATEDIFF(product_shop.date_add,
DATE_SUB(NOW(), INTERVAL 5 DAY)) > 0 AS nouveau,
product_shop.price AS orderprice,
(SELECT COUNT(pc.id_product_comment)
FROM pss_product_comment pc
WHERE pc.id_product = cp.id_product) cmnt_nbr,
(SELECT (SUM(pc.`grade`) / COUNT(pc.`grade`))
FROM `pss_product_comment` pc
WHERE pc.`id_product` = cp.id_product
AND pc.`deleted` = 0
AND pc.`validate` = 1) etoile
FROM
pss_category_product cp
LEFT JOIN
pss_product p ON p.id_product = cp.id_product
INNER JOIN
pss_tax t ON p.id_tax_rules_group = t.id_tax
INNER JOIN
pss_product_shop product_shop ON (product_shop.id_product = p.id_product
AND product_shop.id_shop = 1)
LEFT JOIN
pss_product_attribute pa ON (p.id_product = pa.id_product)
LEFT JOIN
pss_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
pss_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
pss_category_lang cl ON (product_shop.id_category_default = cl.id_category
AND cl.id_lang = 1
AND cl.id_shop = 1)
LEFT JOIN
pss_product_lang pl ON (p.id_product = pl.id_product
AND pl.id_lang = 1
AND pl.id_shop = 1)
LEFT JOIN
pss_image i ON (i.id_product = p.id_product)
LEFT JOIN
pss_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
pss_image_lang il ON (image_shop.id_image = il.id_image
AND il.id_lang = 1)
LEFT JOIN
pss_manufacturer m ON m.id_manufacturer = p.id_manufacturer
LEFT JOIN pss_specific_price sp ON sp.id_product = p.id_product
WHERE
product_shop.id_shop = 1
AND cp.id_category = 3
AND product_shop.active = 1
AND product_shop.visibility IN ('both' , 'catalog')
GROUP BY product_shop.id_product
ORDER BY p.price desc
LIMIT 0 , 10 |
Partager