node.js - 在 where 子句中对 hasMany 关联的属性进行后续过滤

Question

我有以下型号:

const Machine = sequelize.define('Machine', { name: DataTypes.STRING });
const Review = sequelize.define('Review', { ReviewDate: DataTypes.DATEONLY });

Review.belongsTo(Machine);
Machine.hasMany(Review);

现在我想获取上次审查日期超过 3 个月的所有机器。

const reviewOverdueDate = new Date();
reviewOverdueDate.setMonth(reviewOverdueDate.getMonth() - 3);

const request = {
  include: [{
    model: Review,
    order: [
      ["ReviewDate", "DESC"]
    ],
    limit: 1
  }],
  where: {
    // this obviously does not work, because the included Review is an array
    // but maybe there is some syntax to access the first entry in that array?
    '$Review.ReviewDate$': { [Op.gt]: reviewOverdueDate }
  }


Machine.findAndCountAll(request)

如何编写适当的 where 子句来实现目标？有没有办法使用 $nested.column$ 语法来访问列表/数组？
我在 where 子句中使用了 sequelize 文字

where: {
  Sequelize.literal(`('${reviewOverdueDate.toISOString()}' > (SELECT "ReviewDate" FROM 
  "Review" WHERE "Review"."MachineID" = "Machine"."ID" ORDER BY 
  "Review"."ReviewDate" DESC LIMIT 1))`)
}

但我喜欢用 Sequelize 表示法并使用 hasMany 关联。

Answer 1

您是否尝试过在 de include 中使用 where 子句？

const request = {
  include: [{
    model: Review,
    order: [
      ["ReviewDate", "DESC"]
    ],
    limit: 1,
    where: {
      ReviewDate: { [Op.gt]: reviewOverdueDate }
    }
  }]