0

I'm using sequelize (postgreSQL) in my node application. Now I'm trying to change my user roles to project based roles.

So far it looked like:

 User.belongsToMany(Project, { through: 'ProjectUser' }),
 User.belongsToMany(Role, { through: 'RoleUser' }),
 Project.belongsToMany(User, { through: 'ProjectUser' }),
 Role.belongsToMany(User, { through: 'RoleUser' })

But in this case the if I allow an action for a user than it is allowed in all projects. So my thought was to use just one join table with the three references called ProjectRoleUser.

And now I use:

User.hasMany(ProjectRoleUser),
User.belongsToMany(Project, { through: 'ProjectRoleUser' }),
User.belongsToMany(Role, { through: 'ProjectRoleUser' }),
Project.hasMany(ProjectRoleUser),
Project.belongsToMany(User, { through: 'ProjectRoleUser' }),
Role.hasMany(ProjectRoleUser),
Role.belongsToMany(User, { through: 'ProjectRoleUser' })

This works perfectly fine. So the join table looks like:

ProjectId, RoleId, UserId
1, 2, 1
1, 1, 1
2, 1, 1
2, 3, 1
1, 1, 2
2, 1, 2

Is there a nice way to request a result like:

{
   "id": 1,
   "username": "testuser",
   "Projects": [
      {
         "id": 1,
         "projectname": "sampleproject1",
         "Roles": [
            {
               "id": 1,
               "rolename": "Viewer"
            }, {
               "id": 2,
               "rolename": "Author"
            }
         ]
      }, {
         "id": 2,
         "projectname": "second project",
         "Roles": [
            {
               "id": 1,
               "rolename": "Viewer"
            }, {
               "id": 3,
               "rolename": "Co-Author"
            }
         ]
      }
   ]
}, {
... 
}

Can't get my head around how to include that into one query. Any help would be nice..

EDIT:

I got now a solution which works for now. But it is not really clean. Anyone knows a easier way instead of using three includes?

User.findAll({
        include: [{ 
            model: Project,
            include: [{
                model: Role,
                include: [{
                    model: ProjectRoleUser,
                    attributes: [],
                    where: { UserId: { $col: 'User.id' } }
                }]
            }]
        }]
    })
sking
  • 23
  • 5
  • did you try `User.findAll(..., {include: [Project, Role]});`? what was its output? – Louay Alakkad Dec 27 '15 at 18:02
  • yes, but there I don't get the Roles inside the Project. I figured now out a way to get my required result. But it is not really clean. See my edit on the post.. – sking Dec 27 '15 at 18:36
  • A very simple & cleaner way would be to query the `ProjectRoleUser` table. But you'll end up with a little bit different output. – Louay Alakkad Dec 27 '15 at 20:16
  • `ProjectRoleUser.findAll(..., {include: [User, Project, Role]});` – Louay Alakkad Dec 27 '15 at 20:16
  • Thanks, but it is still not what I want. I go now with my solution from my post edit. But another question, maybe you have also a idea about that? See: http://stackoverflow.com/questions/34483863/sequelize-how-to-get-a-list-of-nested-table-entries-project-task-through-m – sking Dec 28 '15 at 12:10

0 Answers0