在where子句中为hasMany关联的属性设置Sequelize筛选器

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))`)
}

但我喜欢用顺序表示法，并使用hasMany关联。

Answer 1

您尝试过在de-include中使用where子句吗？

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