Sequelize findAll with association and foreign key

Question

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

Aralizer Aralizer · Accepted Answer · 2016-06-14T15:19:35

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) {
     // ... });

Sequelize findAll with association and foreign key

1 Answers