Node.js中Sequelize如何实现三张表的INNER JOIN关联查询？

1. 先修正关联配置的错误 #

你当前的关联配置存在两个问题：

• Accept关联Complaint时的外键ComplaintId末尾多了空格，导致外键匹配失败
• 重复定义了两次Complaint和Accept的关联，会产生冲突

修正后的关联配置如下：

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', targetKey: 'id' });

2. 调整查询代码 #

你原代码的问题是将Video_Ref和Accept平级放在Vehicle的include配置中，但Vehicle和Video_Ref没有直接关联，需要按照关联链嵌套配置，同时增加required: true实现INNER JOIN效果。

写法1：以Video_Ref为主模型（和原生SQL逻辑完全对齐） #

const result = await Video_Ref.findAll({
  include: [
    {
      model: Accept,
      required: true,
      include: [
        {
          model: Vehicle,
          required: true,
          where: { vehicleNumber: 'BG345' }
        }
      ]
    }
  ]
})

注意：如果你的关联配置中定义了as别名，需要在include中补充对应的as参数

写法2：保留以Vehicle为主模型的写法 #

const foundVehicleList = await Vehicle.findAll({
  where: { vehicleNumber: 'BG345' },
  include: [
    {
      model: Accept,
      as: 'Accept',
      required: true,
      include: [
        {
          model: Complaint,
          required: true,
          include: [
            {
              model: Video_Ref,
              as: 'Video_Ref',
              required: true,
              attributes: []
            }
          ],
          attributes: []
        }
      ],
      attributes: []
    }
  ],
  attributes: [
    [Sequelize.literal('Accept.ComplaintId'), 'ComplaintId']
  ]
})

内容的提问来源于stack exchange，提问作者Yasiru Ayeshmantha