SELECT
    *
FROM
    (SELECT
        p.product_id,
            (SELECT
                    AVG(rating) AS total
                FROM
                    oc_review r1
                WHERE
                    r1.product_id = p.product_id
                        AND r1.status = '1'
                GROUP BY r1.product_id) AS rating,
            (SELECT
                    price
                FROM
                    oc_product_discount pd2
                WHERE
                    pd2.product_id = p.product_id
                        AND pd2.customer_group_id = '1'
                        AND pd2.quantity = '1'
                        AND ((pd2.date_start = '0000-00-00'
                        OR pd2.date_start < NOW())
                        AND (pd2.date_end = '0000-00-00'
                        OR pd2.date_end > NOW()))
                ORDER BY pd2.priority ASC , pd2.price ASC
                LIMIT 1) AS discount,
            (SELECT
                    price
                FROM
                    oc_product_special ps
                WHERE
                    ps.product_id = p.product_id
                        AND ps.customer_group_id = '1'
                        AND ((ps.date_start = '0000-00-00'
                        OR ps.date_start < NOW())
                        AND (ps.date_end = '0000-00-00'
                        OR ps.date_end > NOW()))
                ORDER BY ps.priority ASC , ps.price ASC
                LIMIT 1) AS special
    FROM
        oc_category_path cp
    LEFT JOIN oc_product_to_category p2c ON (cp.category_id = p2c.category_id)
    LEFT JOIN oc_product p ON (p2c.product_id = p.product_id)
    LEFT JOIN oc_manufacturer m ON (p.manufacturer_id = m.manufacturer_id)
    LEFT JOIN oc_product_description pd ON (p.product_id = pd.product_id)
    LEFT JOIN oc_product_to_store p2s ON (p.product_id = p2s.product_id)
    WHERE
        IF(`p`.`quantity` > 0, 7, `p`.`stock_status_id`) IN (7)
            AND (FIND_IN_SET(1189, `p`.`mfilter_values`))
            AND `p`.`status` = '1'
            AND `p`.`date_available` <= NOW()
            AND `p2c`.`category_id` IN (SELECT
                `category_id`
            FROM
                `oc_category_path`
            WHERE
                `path_id` IN (108))
            AND `p`.`quantity` > 0
            AND NOT (FIND_IN_SET(1042, `p`.`mfilter_values`))
            AND pd.language_id = '2'
            AND p.status = '1'
            AND p.quantity > '0'
            AND p.date_available <= NOW()
            AND p2s.store_id = '0'
            AND `cp`.`path_id` IN (108)
    GROUP BY p.product_id
    ORDER BY p.date_added DESC , LCASE(pd.name) DESC) AS `tmp`
LIMIT 0 , 20