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') . "'"