mysql - Sequelize 等效范围

Question

我想删除日期晚于今天 - 7 且状态类似于“已取消”的行。

如何为该原始查询创建等效的 Sequelize 范围:

SELECT `st_shipment_header`.`id`, 
 FROM `st_shipment_header` AS `st_shipment_header` 
 LEFT OUTER JOIN `st_shipment_event` AS `st_shipment_events` ON `st_shipment_header`.`id` = `st_shipment_events`.`stShipmentHeaderId`
 WHERE (`st_shipment_header`.`id` 
 NOT IN 
 ( SELECT `st_shipment_header`.`id` FROM `st_shipment_header` AS `st_shipment_header` 
 LEFT OUTER JOIN `st_shipment_event` AS `st_shipment_events` ON `st_shipment_header`.`id` = `st_shipment_events`.`stShipmentHeaderId` 
 WHERE (`st_shipment_events`.`date` < NOW() - INTERVAL 7 DAY ) AND (`st_shipment_header`.`shipmentStatus` LIKE 'Canceled%'))));

我已尝试将此作为 Sequelize 范围:

StShipmentHeader.addScope('test', {
  include: [
    { model: models.st_shipment_event }
  ],
  where: {
    [Op.notIn]: {
      [Op.and]: {
        'models.st_shipment_event.date': {
          [Op.lt]: new Date().setDate(new Date().getDate() - 7)
        },
        shipmentStatus: {
          [Op.like]: '%Canceled%'
        }
      }
    }
  }
})

但看起来[Op.notIn]不起作用...

Answer 1

我最终通过创建不同的 SQL 查询(没有 NOT IN)解决了这个问题。

    SELECT 
        `st_shipment_header`.`id`
    FROM
        `st_shipment_header`
            LEFT OUTER JOIN
        `st_shipment_event` AS `st_shipment_events` ON `st_shipment_header`.`id` = `st_shipment_events`.`stShipmentHeaderId`
    WHERE
        ((`st_shipment_header`.`shipmentStatus` LIKE 'Canceled%'
            AND `st_shipment_events`.`date` > NOW() - INTERVAL 7 DAY)
            OR `st_shipment_header`.`shipmentStatus` NOT LIKE 'Canceled%');

    StShipmentHeader.addScope('test', {
      include: [
        models.st_shipment_event,
      ],
      where: {
        [Op.and]: {
          [Op.or]: {
            shipmentStatus: {
              [Op.notLike]: 'Canceled%'
            },
            [Op.and]: {
              shipmentStatus: {
                [Op.like]: 'Canceled%'
              },
              'models.st_shipment_event': sequelize.where(
                sequelize.col('st_shipment_events.date'),
                {
                  [Op.gt]: sequelize.literal('NOW() - INTERVAL 7 DAY')
                }
              )
            }
          },
        }
      }
    })

主要变化:

• 使用sequelize.col来标识列，
• 使用 sequelize.literal('NOW() - INTERVAL 7 DAY'),
• 使用不同的 SQL 查询