CODE
SELECT p.*, product_shop.*, stock.`out_of_stock`, IFNULL(stock.`quantity`, 0) as quantity,
            pl.`description`, pl.`description_short`, pl.`link_rewrite`, pl.`meta_description`,
            pl.`meta_title`, pl.`name`, pl.`available_now`, pl.`available_later`,
            image_shop.`id_image` id_image, il.`legend`, m.`name` AS manufacturer_name,
            product_shop.`date_add` > "2024-12-13" as new,product_attribute_shop.`minimal_quantity` AS product_attribute_minimal_quantity,
                IFNULL(product_attribute_shop.`id_product_attribute`, 0) id_product_attribute
FROM `ps_product` p
 
            INNER JOIN `ps_product_shop` product_shop
                ON(product_shop.`id_product` = p.`id_product` 
                AND product_shop.`id_shop` = 1)
INNER JOIN `ps_product_lang` pl
            ON(
            p.`id_product` = pl.`id_product`
            AND pl.`id_lang` = 2 
            AND pl.`id_shop` = 1 )
INNER JOIN `ps_image_shop` image_shop
            ON(image_shop.`id_product` = p.`id_product`
            AND image_shop.`cover` = 1
            AND image_shop.`id_shop` = 1
            AND COALESCE(image_shop.`id_lang`, 0) IN (0, 2)
        )
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`
        )
LEFT JOIN `ps_product_attribute_shop` `product_attribute_shop`
            ON(p.`id_product` = product_attribute_shop.`id_product`
                AND product_attribute_shop.`default_on` = 1
                AND product_attribute_shop.`id_shop` = 1)

            INNER JOIN `ps_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 )
WHERE (product_shop.`active` = 1)
AND (product_shop.`visibility` IN ("both", "catalog"))
AND (product_shop.`date_add` > "2024-12-13")
AND (EXISTS(
                    SELECT 1 
                    FROM `ps_category_product` cp
                    INNER JOIN `ps_category_group` cg
                        ON(cp.`id_category` = cg.`id_category`
                        AND cg.`id_group` IN (1))
                    WHERE cp.`id_product` = p.`id_product`
                )
            )
AND (stock.`quantity` > 0 )
ORDER BY product_shop.`date_add` DESC
LIMIT 8