SELECT
    p.product_id,p.stock_status_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
    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 = '1297'
GROUP BY p.product_id
ORDER BY p.date_added DESC , LCASE(pd.name) DESC
LIMIT 0 , 20