[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 = 201 ) 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 96, 24]
--
Unknown column 'rating' in 'order clause'