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 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.
Is there any way to join across two junction tables without resorting to raw SQL?
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.
Assuming the following relations:
User.hasMany(Team, { through: 'users_teams'});
Team.hasMany(User, { through: 'users_teams'});
Folder.hasMany(Team, { through: 'teams_folders'});
Team.hasMany(Folder, { through: 'teams_folders'});
You should be able to load everything in one go using nested includes:
User.findAll({
include: [
{
model: Team,
include: [
Folder
]
}
]
});
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