Sequelize findAll with association and foreign key
Assuming that your User
model is linked to the Role
model, something like this should work:
models.Projects.findAll({
include:[
{
model: models.User,
as:'users',
through: {attributes: []},
include: [models.Role]
}] }).then(function(result) {
// ... });
Raptack
Updated on June 14, 2022Comments
-
Raptack almost 2 years
I'm using sequelize to model a mySql-database schema in my node-application. Let's say I have 3 table: Project, User and Role.
It's a "Many to Many" association between Project and User through "Project_User" where is defined the role of a user for a project.
Project Model :
var Project = sequelize.define('Project', { name:{type: DataTypes.STRING, unique: true} }, classMethods: { associate: function(models) { Project.belongsToMany(models.User, { through: 'Project_User', as: 'users'}); } } // Methods... );
User Model :
var User= sequelize.define('User', { name:{type: DataTypes.STRING, unique: true} }, classMethods: { associate: function(models) { User.belongsToMany(models.Project, { through: 'Project_User', as: 'projects'}); } } // Methods... );
And here is the association table Project_User Model :
var Project_User = sequelize.define('Project_User', { role: { type: DataTypes.INTEGER, allowNull: false, references: 'Role', referencesKey: 'id' } },{ classMethods: { associate: function(models) { Project_User.belongsTo(models.Role, {foreignKey: 'role'}); } } });
Now, I want to find all project, with their users and their role. So I've used findAll with the "include" parameters like below:
models.Projects.findAll({ include:[ { model: models.User, as:'users', through: {attributes: ['role'], as: 'role'} }] }).then(function(result) { // ... });
This works great but I only have the roleId associated to the user. I wasn't be able to link this "roleId" with the role table to get the other attributes like role name...
Here is the JSON I've got :
[ { "id": 1, "name": "Project name", "users": [ { "id": 1, "name": "User name", "role": { "role": 1 } } ] } ]
But I would like to have something like that :
[ { "id": 1, "name": "Project name", "users": [ { "id": 1, "name": "User name", "role": { "id": 1, "name": "Role name", "description": "Some info...", } } ] } ]
I've tried many things to realize this association, even successive includes but it was unsuccessful. What is needed in the findAll options to get this JSON result ?
Thanks