sequelize.js - 如何在 sequelize 上过滤关联表或父表

Question

如何在 sequelize 中过滤子(关联)或父表？

我想按 child 的创建日期选择数据，但如果父表没有 child ，我想按 parent 的创建日期选择。

所以我需要过滤器来比较父字段和子字段。

例如:
有没有办法在不使用文字的情况下使用 sequelize 执行以下查询？

SELECT * FROM owner, car
WHERE (car.owner_id IS NULL AND car.created_at=NOW())
OR (car.owner_id IS NOT NULL AND owner.created_at=NOW())

我无法在 Op.OR 中添加包含。

我也尝试了以下代码，但它只考虑父级的 created_at 字段:

where: {
  [Op.or]: {
    [Op.and]: {
      owner_id: null,
      created_at: {
        [Op.between]: [startOfDay(searchDate), endOfDay(searchDate)],
      },
    },
    [Op.and]: {
      owner_id: { [Op.not]: null },
      created_at: {
        [Op.between]: [startOfDay(searchDate), endOfDay(searchDate)],
      },
    }
  }
}

谢谢!!

Answer 1

考虑到模型的名称是 Car 和 Owner :

Owner.findAll({
  include: [
    {
      model: Car
    }
  ],
  attributes: ['some_attribute', 'another_attribute'], //Filter attributes
  where: {
    [Op.or]: {
      [Op.and]: {
        '$Car.owner_id$': { [Op.is]: null },
        '$Car.created_at$': {
          [Op.between]: [startOfDay(searchDate), endOfDay(searchDate)],
        },
      },
      [Op.and]: {
        '$Car.owner_id$': { [Op.not]: null },
        created_at: { // This attribute is referencing owner's created_at
          [Op.between]: [startOfDay(searchDate), endOfDay(searchDate)],
        },
      }
    }
  }
})

您可以使用 '$$' 字符串在 where 子句中引用 Car，看看它是否适合您。