SELECT SQL_CALC_FOUND_ROWS pds.*, ct.name AS category_name, ct.name_en AS category_name_en FROM
(SELECT pd.*, MAX(ps.value) AS prodSaleValue, MAX(sl.value) AS catSaleValue, (100-COALESCE(MAX(sl.value),0))/100*(100-COALESCE(MAX(ps.value),0))/100*pd.price AS finalPrice, IF(pd.added_time < '2019-01-18 10:17:07', false, true) AS isNew
FROM product pd
LEFT JOIN product_category pc ON pc.product_id = pd.id
LEFT JOIN sale_category sc ON sc.category_id = pc.category_id
LEFT JOIN sale sl ON sl.id = sc.sale_id AND sl.date_start < '2019-02-18 10:17:07' AND sl.date_end > '2019-02-18 10:17:07' AND sl.active = 1
LEFT JOIN product_sale ps ON ps.product_id = pd.id AND pd.active = 1 AND ps.date_start < '2019-02-18 10:17:07' AND ps.date_end > '2019-02-18 10:17:07'
LEFT JOIN product_secret psc ON pd.id = psc.product_id LEFT JOIN secret_recipe sr ON psc.secret_id = sr.id
WHERE pd.active = 1 GROUP BY pd.id
ORDER BY finalPrice DESC) AS pds
LEFT JOIN product_category pc2 ON pc2.product_id = pds.id
LEFT JOIN category ct ON ct.id = pc2.category_id WHERE ct.id = ? GROUP BY pds.id