how to write a complex query in AR

there is a sql looks the following

I dont know how to select the subquery "Select"

are there some way to convert it to AR, or QueryBuilder?

or there is a way to split it into some simple statement?




SELECT

            DISTINCT *,

            pd.name AS name,

            p.image,

            m.name AS manufacturer,

            (SELECT price FROM oc_product_discount pd2

                WHERE pd2.product_id = p.product_id

                //AND pd2.customer_group_id = '" . (int)$customer_group_id. "'

                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 = '" . (int)$customer_group_id . "'

                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,

            (SELECT points FROM oc_product_reward pr

                WHERE pr.product_id = p.product_id

                AND customer_group_id = '" . (int)$customer_group_id. "') AS reward,

            (SELECT ss.name FROM oc_stock_status ss

                WHERE ss.stock_status_id = p.stock_status_id

                AND ss.language_id = '". (int)$this->config->get('config_language_id') . "') AS stock_status,

            (SELECT wcd.unit FROM oc_weight_class_description wcd

                WHERE p.weight_class_id = wcd.weight_class_id

                AND wcd.language_id = '" . (int)$this->config->get('config_language_id'). "') AS weight_class,

            (SELECT lcd.unit FROM oc_length_class_description lcd

                WHERE p.length_class_id = lcd.length_class_id

                AND lcd.language_id = '" . (int)$this->config->get('config_language_id'). "') AS length_class,

            (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 COUNT(*) AS total FROM oc_review r2

                WHERE r2.product_id = p.product_id

                AND r2.status = '1'

                GROUP BY r2.product_id) AS reviews,

            p.sort_order

            FROM oc_product p

            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)

            LEFT JOIN oc_manufacturer m ON (p.manufacturer_id = m.manufacturer_id)

            WHERE p.product_id = '". (int)$product_id . "'

            AND pd.language_id = '" . (int)$this->config->get('config_language_id'). "'

            AND p.status = '1'

            AND p.date_available <= NOW()

            AND p2s.store_id = '" . (int)$this->config->get('config_store_id') . "'"