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 < '2018-04-21 08:51:28', 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 < '2018-05-21 08:51:28' AND sl.date_end > '2018-05-21 08:51:28' AND sl.active = 1 LEFT JOIN product_sale ps ON ps.product_id = pd.id AND pd.active = 1 AND ps.date_start < '2018-05-21 08:51:28' AND ps.date_end > '2018-05-21 08:51:28' 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