node.js - Sequelize 嵌套关联与两个表

Question

我有一个场景，我试图查询一个父表(文档)和两个关联表(引用和用户)，这两个表彼此没有关系，但与父表有关系。在 SQL 中，此查询看起来像这样并正确输出我正在寻找的数据:

select *
from `document`
left join `user`
on `document`.`user_id` = `user`.`user_id`
left join `reference`
on `document`.`reference_id` = `reference`.`reference_id`
where `user`.`organization_id` = 1;

但是，嵌套的关联必须按层次顺序关联才能使查询起作用。由于嵌套关联彼此不相关，因此出现关联错误。我怎样才能避免这个错误？ required: false 对此有什么影响吗？

models.Document.findAll({
  order: 'documentDate DESC',
  include: [{
    model: models.User,
    where: { organizationId: req.user.organizationId },
    include: [{
      model: models.Reference,
    }]
  }],
})

错误:

Unhandled rejection Error: reference is not associated to user!

关联:

文档:

associate: function(db) {
  Document.belongsTo(db.User, {foreignKey: 'user_id'}),
    Document.belongsTo(db.Reference, { foreignKey: 'reference_id'});;
}

引用:

associate: function(db){
  Reference.hasMany(db.Document, { foreignKey: 'reference_id' });
}

我应该改为链式查询吗？

Answer 1

如果您想(尽可能接近地)复制您的查询，请使用以下查询。请记住 where关于用户include只会用于删除 Document.user_id 上的匹配项User.organization_id在哪里不匹配，但是 Document仍然会被退回。如果要省略 User.organization_id 所在的文档不匹配使用 required: true .

User <- Document -> Reference

models.Document.findAll({
  // this is an array of includes, don't nest them
  include: [{
    model: models.User,
    where: { organization_id: req.user.organization_id }, // <-- underscored HERE
    required: true, // <-- JOIN to only return Documents where there is a matching User
  },
  {
    model: models.Reference,
    required: false, // <-- LEFT JOIN, return rows even if there is no match
  }],
  order: [['document_date', 'DESC']], // <-- underscored HERE, plus use correct format
});