SELECT
p.*, product_shop.*,
stock.out_of_stock, IFNULL(stock.quantity, 0) as quantity, pl.`description`, pl.`description_short`, pl.`available_now`, pl.`available_later`,
IFNULL(product_attribute_shop.id_product_attribute, 0) id_product_attribute,
pl.`link_rewrite`, pl.`meta_description`, pl.`meta_keywords`, pl.`meta_title`,
pl.`name`, image_shop.`id_image` id_image, il.`legend`, m.`name` AS manufacturer_name, attr_lang.`name` as name_variant,
DATEDIFF(
p.`date_add`,
DATE_SUB(
"2019-04-25 00:00:00",
INTERVAL 20 DAY
)
) > 0 AS new
FROM `ps8l_product` p
INNER JOIN ps8l_product_shop product_shop
ON (product_shop.id_product = p.id_product AND product_shop.id_shop = 1)
LEFT JOIN `ps8l_product_attribute_shop` product_attribute_shop
ON (p.`id_product` = product_attribute_shop.`id_product` AND product_attribute_shop.id_shop=1)
LEFT JOIN `ps8l_specific_price` sp ON (product_attribute_shop.id_product_attribute = sp.id_product_attribute)
LEFT JOIN ps8l_stock_available stock
ON (stock.id_product = p.id_product AND stock.id_product_attribute = 0 AND stock.id_shop = 1 AND stock.id_shop_group = 0 )
LEFT JOIN `ps8l_product_lang` pl ON (
p.`id_product` = pl.`id_product`
AND pl.`id_lang` = 1 AND pl.id_shop = 1
)
LEFT JOIN `ps8l_product_attribute_combination` pro_attr_comb ON (sp.id_product_attribute = pro_attr_comb.id_product_attribute)
LEFT JOIN `ps8l_attribute_lang` attr_lang ON (pro_attr_comb.id_attribute = attr_lang.id_attribute and attr_lang.id_lang = pl.id_lang)
LEFT JOIN `ps8l_image_shop` image_shop
ON (image_shop.`id_product` = p.`id_product` AND image_shop.cover=1 AND image_shop.id_shop=1)
LEFT JOIN `ps8l_image_lang` il ON (image_shop.`id_image` = il.`id_image` AND il.`id_lang` = 1)
LEFT JOIN `ps8l_manufacturer` m ON (m.`id_manufacturer` = p.`id_manufacturer`)
WHERE product_shop.`active` = 1
AND NOT EXISTS( SELECT * FROM `ps8l_product_attribute` as pa
LEFT JOIN `ps8l_specific_price` as spne ON pa.id_product_attribute =spne.id_product_attribute WHERE pa.id_product_attribute = product_attribute_shop.id_product_attribute AND spne.id_product_attribute IS NULL
AND NOT EXISTS ( SELECT * FROM `ps8l_product_attribute` as 2pa
INNER JOIN `ps8l_specific_price` as 2spne ON 2pa.id_product = 2spne.id_product WHERE 2pa.id_product = pa.id_product AND 2spne.id_product_attribute = 0))
AND product_shop.`show_price` = 1
AND p.`visibility` IN ("both", "catalog")
AND p.`id_product` IN (16, 18, 19, 20, 21, 17, 23, 13, 122, 7, 28, 25, 162, 89)
AND EXISTS(SELECT 1 FROM `ps8l_category_product` cp
JOIN `ps8l_category_group` cg ON (cp.id_category = cg.id_category AND cg.`id_group` = 1)
WHERE cp.`id_product` = p.`id_product`)
ORDER BY product_shop.price DESC
LIMIT 0, 20
production and sale of quality natural food for all pets - Casapet
