sequelize nodejs Inner-join with 3 tables?

Question

这是我的本地查询，它包括2个内部连接。我可以一个接一个地写。但我想写这个查询从nodejs Sequelize。

SELECT * 
FROM video_refs r
JOIN accepts a ON a.ComplaintId = r.complaint_id
JOIN vehicles v ON v.acceptId = a.id
WHERE v.vehicleNumber = 'BG345'

我试过了，但是这个Video_Ref部分不工作

const foundVehicleList =await Vehicle.findAll({
where: {
vehicleNumber:'BG1234',

},
include: [                                               
{ model: Accept, as: 'Accept', attributes: []},   
{ model: Video_Ref, as: 'Video_Ref', attributes: []},   
],
attributes: [

[Sequelize.literal('Accept.ComplaintId'),'ComplaintId']
]

});

这些是关系

db.Complaint.hasOne(db.Video_Ref,{foreignKey: 'complaint_id', sourceKey: 'id'});
db.Video_Ref.belongsTo(db.Complaint,{foreignKey: 'complaint_id', targetKey: 'id'});
db.Complaint.hasOne(db.Accept,{foreignKey: 'ComplaintId ', sourceKey: 'id'});
db.Accept.belongsTo(db.Complaint,{foreignKey: 'ComplaintId ', targetKey: 'id'});
db.Accept.hasMany(db.Vehicle, {foreignKey: 'acceptId', sourceKey: 'id'});
db.Vehicle.belongsTo(db.Accept, {foreignKey: 'acceptId', sourceKey: 'id'});
db.Complaint.hasOne(db.Accept,{foreignKey: 'ComplaintId', sourceKey: 'id'});
db.Accept.belongsTo(db.Complaint,{foreignKey: 'ComplaintId ', targetKey: 'id'});

Answer 1

我找到了这样的答案

SELECT vf.id,vf.reference FROM video_refs vf INNER JOIN complaints c ON vf.`complaint_id` = c.`id` INNER JOIN Accepts a ON c.`id` = a.`ComplaintId` INNER JOIN vehicles vh ON a.`id` = vh.`acceptId` AND vh.`vehicleNumber` = 'BG1234' AND vh.`createdAt` >= '2021-09-06 09:11:38'
const VideoRefList=await Video_Ref.findAll({
include : [
{
model: Complaint,
required: true,
attributes: [],
include: [
{
model: Accept,
required: true,
attributes: [],
include : [
{
model: Vehicle,
required: true,
attributes: [],
where: {
vehicleNumber:'BG1234',
createdAt: {
[Op.gte]: moment().subtract(7, 'days').toDate()
}
},
}
]
}
]
}
],
attributes: [
'id',
'reference'
]
});

Answer 2

这是一个如何完成3个表的nodejs内部连接的例子。我用用户和团队成员表加入团队

"const members = teams.findAll({包括:［{model: user, as: owner;}，{model: team_member, as: "members"，包括:[user]}，]，});

"