Sequelize findall for every findall is possible?

Question

I have this tables. Clients have Projects and Users works in Projects

Clients
- id
- name

Projects
- id
- name
- client_id

Users
- id
- name

UserProject
- user_id
- project_id

I try to return all users of the every project of client for example id=1 Finally result, something like this JSON:

[{
   id:1
   name:"Project1"
   users:[{
            id:23
            name:"Robert Stark"
          },{
            id:67
            name: "John Snow"
          }]
 }, {
   id:2
   name:"Project2"
   users:[{
            id:1
            name:"Aria Stark"
          }]
}]

If I find projects it works fine

req.tables.Project.findAll({
    where: {
        client_id:1
    }
 }).success(function(projects) {
          ...

If I find Users of a project it works fine

req.tables.UserProject.findAll({
   where: {
       project_id:1
   },
   include: [
       { model: req.tables.User, as: 'User' }
   ]
}).success(function(UsersProject) {
     ...

But, how can I combine both finAlls to return all users in every project? Something like the next code, but that works well. How can I do it? I found this: Node.js multiple Sequelize raw sql query sub queries but It doesn't work for me or I do not know how to use it, because I have 2 loops not only one. I have projects loop and users loop

req.tables.Project.findAll({
    where: {
        client_id:1
    }
}).success(function(projects) {

    var ret_projects=[];

    projects.forEach(function (project) {

         var ret_project={
             id:project.id,
             name:project.name,
             data:project.created,
             users:[]
         });

         req.tables.UserProject.findAll({
             where: {
                 project_id:project.id
             },
             include: [
                 { model: req.tables.User, as: 'User' }
             ]
         }).success(function(UsersProject) {

             var ret_users=[];

             UsersProject.forEach(function (UserProject) {
                 ret_users.push({
                     id:UserProject.user.id,
                     name:UserProject.user.name,
                     email:UserProject.user.email
                  });
             });
             ret_project.users=ret_users;
             ret_project.push(ret_project)
         });
     });

     res.json(projects);
});

Answer 1

Sounds like you already have a solution, but I came across the same issue and came up with this solution.

Very similar to what cvng said, just using nested include. So use:

Project.belongsTo(Client);
Project.hasMany(User);
User.hasMany(Project);

Then:

req.tables.Client.find({
    where: { id:req.params.id },
    include: [{model: req.tables.Project, include : [req.tables.User]}]
  }).success(function(clientProjectUsers) {
    // Do something with clientProjectUsers.
    // Which has the client, its projects, and its users.
  });
}

The ability to 'Load further nested related models' is described through the param 'option.include[].include' here: Sequelize API Reference Model.

Maybe this will be useful to someone else in the future.

Cheers!