我每天有两张table coupons 表和a coupon_city_map table。
IDU代码1供应商202供应商103供应商504供应商405供应商90
优惠券\idu city \id12233442
我需要有身份证的优惠券 1 4, and 5 为了 city_id = 2 .
所以它应该把所有的优惠券拿到哪里去 city_id=2 i、 e.带id的优惠券 1 and 4 它还应该获取没有钥匙的优惠券 coupon_city_map 即 5 .
这是我尝试过的，但问题仍然存在 [Op.or] 不起作用，它将返回所有优惠券。

let coupons = await Coupon.findAll({
      where: {
        [Op.or]: [
          { '$CouponCities.city_id$': city_id },
          { '$CouponCities.coupon_id$': null },
        ],
        ...filters // other filter like is_active: true
      },
      include: {
        model: CouponCity,
        attributes: [],
      },
      attributes: ['id', 'coupon_code', 'discount_per', 'flat_discount', 'discount_upto', 'description', 'display'],
    });

正在生成的查询

SELECT `Coupon`.`id`, 
       `Coupon`.`coupon_code`, 
       `Coupon`.`discount_per`, 
       `Coupon`.`flat_discount`, 
       `Coupon`.`discount_upto`, 
       `Coupon`.`description`, 
       `Coupon`.`display` 
FROM `coupons` AS `Coupon` 
LEFT OUTER JOIN `coupon_city_map` AS `CouponCities` ON `Coupon`.`id` = `CouponCities`.`coupon_id` 
WHERE (`Coupon`.`user_id` IS NULL OR `Coupon`.`user_id` = 1) 
  AND `Coupon`.`is_active` = true 
  AND `Coupon`.`is_external` = false 
  AND `Coupon`.`start_date` < '2020-12-30 10:33:20' 
  AND `Coupon`.`expiry_date` > '2020-12-30 10:33:20';

更新我也尝试下面，但它仍然返回所有的优惠券。

let coupons = await Coupon.findAll({
    // where: {
    //   ...filters,
    // },
    include: {
      model: CouponCity,
      required: false,
      where: {
        [Op.or]: [
          {
            zone_id: zoneId,
          }, {
            coupon_id: null,
          },
        ],
      },
      attributes: [],
    },
    attributes: ['id', 'coupon_code', 'discount_per', 'flat_discount','discount_upto', 'description', 'display'],
 });

…并生成以下查询。

SELECT `Coupon`.`id`, 
       `Coupon`.`coupon_code`, 
       `Coupon`.`discount_per`, 
       `Coupon`.`flat_discount`, 
       `Coupon`.`discount_upto`, 
       `Coupon`.`description`, 
       `Coupon`.`display` 
FROM   `coupons` AS `Coupon` 
       LEFT OUTER JOIN `coupon_city_map` AS `CouponCities` 
                    ON `Coupon`.`id` = `CouponCities`.`coupon_id` 
                       AND ( `CouponCities`.`zone_id` = 1 
                             AND `CouponCities`.`coupon_id` IS NULL ) 
WHERE  `Coupon`.`is_active` = true 
       AND `Coupon`.`is_external` = false;