1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18
| SELECT SQL_CALC_FOUND_ROWS
a.`id_product`,b.name as name,`reference`,a.price as price, shop.name as shopname, a.id_shop_default, MAX(image_shop.id_image) id_image, cl.name `name_category`, sa.`price`, 0 AS price_final, a.`is_virtual`, pd.`nb_downloadable`, sav.`quantity` as sav_quantity, sa.`active`, IF(sav.`quantity`<=0, 1, 0) badge_danger, sav.`quantity_outsale` as sav_quantity_outsale,
(
SELECT SUM( sav.`quantity` - sav.`quantity_outsale` )
FROM `ps_stock_available` sav
WHERE sav.`id_product` = a.`id_product` AND sav.`id_product_attribute` =0 AND sav.id_shop =4 AND sav.id_shop_group =0) AS sav_quantity_dispo
FROM `ps_product` a
LEFT JOIN `ps_product_lang` b ON (b.`id_product` = a.`id_product` AND b.`id_lang` = 5 AND b.`id_shop` = 4
)
LEFT JOIN `ps_image` i ON (i.`id_product` = a.`id_product`)
LEFT JOIN `ps_stock_available` sav ON (sav.`id_product` = a.`id_product` AND sav.`id_product_attribute` = 0 AND sav.id_shop = 4 AND sav.id_shop_group = 0 ) JOIN `ps_product_shop` sa ON (a.`id_product` = sa.`id_product` AND sa.id_shop = 4)
LEFT JOIN `ps_category_lang` cl ON (sa.`id_category_default` = cl.`id_category` AND b.`id_lang` = cl.`id_lang` AND cl.id_shop = 4)
LEFT JOIN `ps_shop` shop ON (shop.id_shop = 4)
LEFT JOIN `ps_image_shop` image_shop ON (image_shop.`id_image` = i.`id_image` AND image_shop.`cover` = 1 AND image_shop.id_shop = 4)
LEFT JOIN `ps_product_download` pd ON (pd.`id_product` = a.`id_product`)
WHERE 1
GROUP BY sa.id_product
ORDER BY a.`id_product` asc LIMIT 0,50 |
Partager