0

Magento 2.2.9

When I try to redeem a coupon code I get an SQL error:

SQLSTATE[42S22]: Column not found: 1054 Unknown column 'rule_coupons.expiration_date' in 'where clause', query was: 
SELECT `t`.*
FROM (
SELECT `main_table`.*, NULL AS `code`
FROM `salesrule` AS `main_table`
INNER JOIN (
SELECT DISTINCT `salesrule_website`.`rule_id`
FROM `salesrule_website`
WHERE (website_id IN (1))) AS `website` ON main_table.rule_id = website.rule_id
INNER JOIN `salesrule_customer_group` AS `customer_group_ids` ON main_table.rule_id = customer_group_ids.rule_id AND customer_group_ids.customer_group_id = 1
WHERE (from_date IS NULL OR from_date <= '2019-11-13') AND (to_date IS NULL OR to_date >= '2019-11-13') AND (`is_active` = '1') AND (main_table.coupon_type = 1) UNION ALL
SELECT `main_table`.*, `rule_coupons`.`code`
FROM `salesrule` AS `main_table`
INNER JOIN (
SELECT DISTINCT `salesrule_website`.`rule_id`
FROM `salesrule_website`
WHERE (website_id IN (1))) AS `website` ON main_table.rule_id = website.rule_id
INNER JOIN `salesrule_customer_group` AS `customer_group_ids` ON main_table.rule_id = customer_group_ids.rule_id AND customer_group_ids.customer_group_id = 1
INNER JOIN `salesrule_coupon` AS `rule_coupons` ON main_table.rule_id = rule_coupons.rule_id AND main_table.coupon_type <> 1 AND rule_coupons.code = 'test'
WHERE (from_date IS NULL OR from_date <= '2019-11-13') AND (to_date IS NULL OR to_date >= '2019-11-13') AND (`is_active` = '1') AND ((rule_coupons.expiration_date IS NULL OR rule_coupons.expiration_date >= '2019-11-13') AND (main_table.coupon_type = 3 AND rule_coupons.type = 1 OR (main_table.coupon_type = 2) AND ((main_table.use_auto_generation = 1 AND rule_coupons.type = 1) OR (main_table.use_auto_generation = 0 AND rule_coupons.type = 0))))) AS `t`
WHERE ((rule_coupons.expiration_date IS NULL) AND (to_date IS NULL OR to_date >= '2019-11-13') OR (rule_coupons.expiration_date IS NOT NULL) AND (rule_coupons.expiration_date >= '2019-11-13')) AND ((main_table.to_date IS NULL) OR (main_table.to_date >= '2019-11-13')) AND (`is_active` = '1')
ORDER BY sort_order ASC

I checked the table and the column expiration_date definately exists. I removed all existing rules and created only one simple rule that generates a discount.

I cannot wrap my head around. Why does it complain about a missing column that definately exists?

Thank you

1 Answer 1

0

Well that error is because you cannot refer the rule_coupons table inside the final WHERE of the main select. rule_coupons is an Select inside of an inner join, because of that you can not refer rule_coupons in the WHERE at the final end.
The same is for main_table, because it is an subselect inside of FROM, it is scoped to the FROM part of your sql statement. You are not able to use main_table outside of the FROM.
The other way around is possible to use an identifier outside the Subselect from within a Subselect, but it is not possible this way around.

You need to join the salesrule_coupon inside your main select, or use some kind of subselect on salesrule_coupon to apply this filters to your main select. The other filters on the main_table can be applied, because you are selecting the according values.

Without the filters on salesrule_coupons the final where could look like this:

WHERE 
 ((to_date IS NULL) OR (to_date >= '2019-11-13')) AND (`is_active` = '1')

and it would work fine. Remember: still without the filters on salesrule_coupon.

Actually, i am not exactly sure what you are trying to do with this huge select, but i guess there is an easier way for a more understandable select, which will return the same ;)

So maybe this is what you want:

SELECT t.* FROM
(
    SELECT sr.*, NULL as `code` FROM salesrule AS sr
    INNER JOIN salesrule_website AS srw ON srw.rule_id=sr.rule_id AND srw.website_id=1
    INNER JOIN salesrule_customer_group AS srcg ON srcg.rule_id=sr.rule_id AND srcg.customer_group_id=1
    WHERE 
    (sr.from_date IS NULL OR sr.from_date <= '2019-11-13') 
    AND (sr.to_date IS NULL OR sr.to_date >= '2019-11-13') 
    AND (sr.`is_active` = '1') 
    AND (sr.coupon_type = 1)

    UNION ALL

    SELECT sr.*, sc.code FROM salesrule AS sr
    INNER JOIN salesrule_website AS srw ON srw.rule_id=sr.rule_id AND srw.website_id=1
    INNER JOIN salesrule_customer_group AS srcg ON srcg.rule_id=sr.rule_id AND srcg.customer_group_id=1
    INNER JOIN salesrule_coupon as sc ON sc.rule_id=sr.rule_id AND sr.coupon_type <> 1 AND sc.code='test'
    WHERE 
    (sr.from_date IS NULL OR sr.from_date <= '2019-11-13')
    AND (sr.to_date IS NULL OR sr.to_date >= '2019-11-13')
    AND (sr.`is_active` = '1')
    AND (
        (sc.expiration_date IS NULL OR sc.expiration_date >= '2019-11-13')
        AND (sr.coupon_type = 3
        AND sc.type = 1 OR (sr.coupon_type = 2)
        AND (
            (sr.use_auto_generation = 1 AND sc.type = 1) 
            OR (sr.use_auto_generation = 0 AND sc.type = 0)
        )
    ))
) AS t
LEFT JOIN salesrule_coupon AS tc ON tc.rule_id=t.rule_id
WHERE 
(
    (tc.expiration_date IS NULL) 
    AND (t.to_date IS NULL OR t.to_date >= '2019-11-13')
    OR (tc.expiration_date IS NOT NULL) 
    AND (tc.expiration_date >= '2019-11-13')
) AND 
(
    (t.to_date IS NULL) 
    OR (t.to_date >= '2019-11-13')
) AND (t.`is_active` = '1')
ORDER BY t.sort_order ASC;
6
  • i guess the UNION ALL can be somehow avoided by using a left join in the first subselect. And this way the SELECT( SELECT can be avoided next etc..
    – Ekk4rd
    Nov 13, 2019 at 12:06
  • I am not trying to do anything with this. This is just somehow the query magento produces when I try to redeem a coupon code. And it fails due to this query. Now I'm kinda lost where to go from here.
    – omsta
    Nov 13, 2019 at 12:10
  • Do you have some module installed which extends the salesrules ? I'm pretty confused if the Magento2 core really generates invalid SQL statements...
    – Ekk4rd
    Nov 13, 2019 at 12:13
  • I disabled all non-magento modules and the error goes away. This will take a while. Will report back when I found the sucker.
    – omsta
    Nov 13, 2019 at 12:31
  • I found that by disabling the module Dotdigitalgroup_Email the error disappears. What confuses me is that I have another store without any problems even with Dotdigitalgroup_Email enabled.
    – omsta
    Nov 13, 2019 at 14:20

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.