使用 Sequelize 连接多个连接表 [英] Join across multiple junction tables with Sequelize

问题描述

我有一个包含三个主表的数据库:users、teams 和 folders 由两个连接表 users_teams<连接/code> 和 teams_folders.用户与团队之间以及团队与文件夹之间存在多对多关系(一个用户可以在多个团队中，而团队可以拥有多个文件夹).

I have a database with three primary tables: users, teams, and folders joined by two junction tables, users_teams and teams_folders. There is a many-to-many relationship between users and teams and between teams and folders (a user can be on more than one team and teams can own more than one folder).

Sequelize 在管理用户-团队和团队-文件夹关系方面做得非常出色，但我找不到在用户和文件夹之间建立关系的方法.

Sequelize does a wonderful job of managing the user-teams and teams-folder relationship, but I can find no way to establish a relationship between users and folders.

有没有办法在不使用原始 SQL 的情况下连接两个联结表?

似乎没有办法优雅地或以合理的步骤数来完成这项工作.我尝试过诸如 user.getFolders()、Folder.findAll({ include: [User] }) 之类的方法，但 Sequelize 似乎无法理解三级层次结构.

There seems to be no way to accomplish this elegantly or in a reasonable number of steps. I have tried methods like user.getFolders(), Folder.findAll({ include: [User] }), but Sequelize doesn't seem to be able to understand a three level hierarchy.

推荐答案

假设如下关系:

User.belongsToMany(Team, { through: 'users_teams'});
Team.belongsToMany(User, { through: 'users_teams'});

Folder.belongsToMany(Team, { through: 'teams_folders'});
Team.belongsToMany(Folder, { through: 'teams_folders'});

您应该能够使用嵌套包含一次性加载所有内容:

You should be able to load everything in one go using nested includes:

User.findAll({
  include: [
    {
      model: Team, 
      include: [
        Folder
      ]  
    }
  ]
});

通过您在帖子中给出的示例，您似乎已经走上了正确的轨道:).您唯一需要更改的是，不是直接在 include 中传递 User 模型，而是传递具有模型属性和进一步嵌套的 include 属性的对象

You seem to be on the right track already with the example you have given in your post :). The only thing you need to change is instead of passing the User model directly in include, you pass an object with a model property and a further nested include property

这篇关于使用 Sequelize 连接多个连接表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！