[SELECT
p.product_id AS id,
product_description.name AS title,
p.model AS model,
p.isbn AS isbn,
p.image AS image,
-- COALESCE(additional_images.image, '') AS image2,
COALESCE(
(SELECT image
FROM oc_product_image
WHERE product_id = p.product_id
ORDER BY sort_order ASC
LIMIT 1),
''
) AS image2,
-- IF(p.quantity > 0, 'in stock', 'out of stock') AS availability,
p.quantity AS quantity,
p.price AS price,
COALESCE(
(SELECT price
FROM oc_product_special
WHERE product_id = p.product_id
AND customer_group_id = 1
AND (date_start = 0 OR date_start <= NOW())
AND (date_end = 0 OR date_end >= NOW())
LIMIT 1),
0
)
AS discountedPrice
FROM oc_product AS p
LEFT JOIN oc_product_description AS product_description
ON (product_description.product_id = p.product_id
AND product_description.language_id = 3)
/*
LEFT JOIN (
SELECT DISTINCT product_id, image
FROM oc_product_image
ORDER BY sort_order ASC
) AS additional_images
ON additional_images.product_id = p.product_id
-- The DISTINCT keyword doesn''t really work the way you''re expecting it to. When you use SELECT DISTINCT col1, col2, col3 you are in fact selecting all unique {col1, col2, col3} tuples.
*/
LEFT JOIN (
SELECT SUM(quantity) AS times_bought, product_id
FROM oc_order_product
WHERE order_id IN (
SELECT order_id
FROM oc_order
WHERE order_status_id IN (5,12)
-- 5 = Покупка завершена, 12 = Отправка Новой Почтой
-- they finish orders on both of those
AND DATEDIFF(NOW(), date_modified) < 90
-- last 90 days
)
GROUP BY product_id
) AS products_bought
ON products_bought.product_id = p.product_id
WHERE p.product_id IN (
SELECT product_id
FROM oc_product_to_category
WHERE category_id = 261
) AND p.status = 1 AND p.date_available <= NOW()
ORDER BY IF(p.quantity > 0, 1, 0) DESC, rating ASC, LCASE(product_description.name) ASC
LIMIT 0, 24]
--
Unknown column 'rating' in 'order clause'