In opencart, there is a sample of products according to a variety of criteria, but I need to add another at the top and bottom levels of the price, and the comparison should involve both price, discounts and promotions. For some reason, checking the subquery gives an error

#1054 - Unknown column 'discount' in 'where clause'

It is clear that this is not in the table, but the field is being formed. Tell me how you can use the resulting value of discount in the condition. (The most not clear that in ORDER BY does not swear, and in WHERE a problem)

 SELECT p.product_id ,(SELECT price FROM me_oc_ga_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 FROM me_oc_ga_product_to_category p2c LEFT JOIN me_oc_ga_product p ON (p2c.product_id = p.product_id) LEFT JOIN me_oc_ga_product_description pd ON (p.product_id = pd.product_id) LEFT JOIN me_oc_ga_product_to_store p2s ON (p.product_id = p2s.product_id) WHERE pd.language_id = '2' AND p.status = '1' AND p.date_available <= NOW() AND p2s.store_id = '0' AND p2c.category_id = '96' AND discount IS NOT NULL GROUP BY p.product_id ORDER BY (CASE WHEN discount IS NOT NULL THEN discount ELSE p.price END) ASC , LCASE(pd.name) ASC LIMIT 0,5
  • 2
    It is impossible to call alias. It is necessary to wrap the request in one more select * from (ваш запрос) A where вот тут можно использовать имена которые были сделаны в запросе - Mike

1 answer 1

The most not clear that in ORDER BY does not swear, and in WHERE a problem

See the order in which SELECT clauses are executed :

 1: FROM 2: WHERE 3: GROUP BY 4: HAVING 5: SELECT 6: ORDER BY

column aliases "appear" in the SELECT , i.e. in the WHERE they simply do not exist yet, but in the ORDER BY they can already be used.

In your case, you can add an additional subquery:

 SELECT T.* FROM( SELECT p.product_id ,(SELECT price FROM me_oc_ga_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 FROM me_oc_ga_product_to_category p2c INNER JOIN me_oc_ga_product p ON (p2c.product_id = p.product_id) INNER JOIN me_oc_ga_product_to_store p2s ON (p.product_id = p2s.product_id) LEFT JOIN me_oc_ga_product_description pd ON (p.product_id = pd.product_id) WHERE pd.language_id = '2' AND p.status = '1' AND p.date_available <= NOW() AND p2s.store_id = '0' AND p2c.category_id = '96' GROUP BY p.product_id ORDER BY (CASE WHEN discount IS NOT NULL THEN discount ELSE p.price END) ASC , LCASE(pd.name) ASC LIMIT 0,5 )T WHERE T.discount IS NOT NULL

Try again this option, theoretically it can run faster, you need to look at the particular data:

 SELECT p.product_id ,(SELECT price FROM me_oc_ga_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 FROM me_oc_ga_product_to_category p2c INNER JOIN me_oc_ga_product p ON (p2c.product_id = p.product_id) INNER JOIN me_oc_ga_product_to_store p2s ON (p.product_id = p2s.product_id) LEFT JOIN me_oc_ga_product_description pd ON (p.product_id = pd.product_id) WHERE pd.language_id = '2' AND p.status = '1' AND p.date_available <= NOW() AND p2s.store_id = '0' AND p2c.category_id = '96' AND EXISTS (SELECT price FROM me_oc_ga_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())) ) GROUP BY p.product_id ORDER BY (CASE WHEN discount IS NOT NULL THEN discount ELSE p.price END) ASC , LCASE(pd.name) ASC LIMIT 0,5

ps: LEFT JOIN 's for the me_oc_ga_product and me_oc_ga_product_to_store not needed, since if there is NULL , the columns listed in WHERE will also be NULL and such rows will not be included in the final sample anyway.

  • I removed part of the query there are two more tables (just subqueries, but it doesn’t have much importance) - iyaki
  • Really had to add a subquery - iyaki