SELECT p.*, ps.*, stock.out_of_stock, IFNULL(stock.quantity, 0) AS quantity, product_attribute_shop.id_product_attribute AS 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` id_image,
        il.`legend`, m.`name` AS manufacturer_name, cl.`name` AS category_default,
        DATEDIFF(ps.`date_add`, DATE_SUB("2019-09-15 00:00:00",
        INTERVAL 20 DAY)) > 0 AS NEW,


        ((p.price + IFNULL((
        SELECT pa_test.price FROM ps_product_attribute pa_test LEFT JOIN ps_stock_available sa_test
        ON(pa_test.id_product=sa_test.id_product AND pa_test.id_product_attribute=sa_test.id_product_attribute)
        WHERE pa_test.id_product=p.id_product AND sa_test.quantity > 0 ORDER BY pa_test.price LIMIT 1), 0)) -
        (p.price + IFNULL((
        SELECT pa_test.price FROM ps_product_attribute pa_test LEFT JOIN ps_stock_available sa_test
        ON(pa_test.id_product=sa_test.id_product AND pa_test.id_product_attribute=sa_test.id_product_attribute)
        WHERE pa_test.id_product=p.id_product AND sa_test.quantity > 0 ORDER BY pa_test.price LIMIT 1), 0))
       * IFNULL((SELECT reduction FROM ps_specific_price WHERE id_product=p.id_product AND reduction_type="percentage" ORDER BY reduction DESC LIMIT 1),0)
       ) AS orderprice,


         (p.price + IFNULL((
                    SELECT pa_test.price FROM ps_product_attribute pa_test LEFT JOIN ps_stock_available sa_test
                    ON(pa_test.id_product=sa_test.id_product AND pa_test.id_product_attribute=sa_test.id_product_attribute)
                    WHERE pa_test.id_product=p.id_product  ORDER BY pa_test.price LIMIT 1
                    ), 0)) AS orderprice_all


         FROM `ps_pm_advancedsearch_cache_product_50` acp
                JOIN `ps_product` p ON (p.`id_product` = acp.`id_product`)


        JOIN `ps_product_shop` ps ON (

            ps.id_shop IN (1)
            AND ps.`id_product` = acp.`id_product`
        )
JOIN `ps_pm_advancedsearch_cache_product_criterion_50` acpc4 ON ( acp.`id_cache_product` = acpc4.`id_cache_product` AND acpc4.`id_criterion` IN (1635))
JOIN `ps_pm_advancedsearch_cache_product_criterion_50` acpc3 ON ( acp.`id_cache_product` = acpc3.`id_cache_product` AND acpc3.`id_criterion` IN (492))
LEFT JOIN `ps_tax_rule` tr ON (ps.`id_tax_rules_group` = tr.`id_tax_rules_group` AND tr.`id_country` = 177 AND tr.`id_state` = 0)
LEFT JOIN `ps_tax` t ON (t.`id_tax` = tr.`id_tax`)
LEFT JOIN `ps_group_reduction` grc ON (grc.`id_group`=1 AND ps.`id_category_default` = grc.`id_category`)
LEFT JOIN `ps_stock_available` sa1 ON ( sa1.`id_product` = acp.`id_product` AND sa1.`id_product_attribute`=0  AND sa1.id_shop = 1  AND sa1.id_shop_group = 0 )
            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
            )
             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  AND stock.id_shop_group = 0  )



        LEFT JOIN `ps_product_lang` pl ON (p.`id_product` = pl.`id_product` AND pl.`id_lang` = 3 AND pl.id_shop = 1 )
        LEFT JOIN `ps_category_lang` cl ON (ps.`id_category_default` = cl.`id_category` AND cl.`id_lang` = 3 AND cl.id_shop = 1 )
            LEFT 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)

        LEFT JOIN `ps_image_lang` il ON (image_shop.`id_image` = il.`id_image` AND il.`id_lang` = 3)
        LEFT JOIN `ps_manufacturer` m ON m.`id_manufacturer` = p.`id_manufacturer`
         WHERE ps.`active` = 1
 AND ps.`visibility` IN ("both", "search")
 AND (IF (sa1.`quantity` > 0, 1, IF (sa1.`out_of_stock` = 2, 0 = 1, sa1.`out_of_stock` = 1))) GROUP BY ps.`id_product` ORDER BY p.on_stock DESC , orderprice ASC,orderprice_all ASC LIMIT 0,24