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);
});

score 6 · Answer 1 · edited May 23 '17 at 12:01

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!

Looks like the 'Sequelize API Reference Model' link above is broken. Here's a more updated link [http://docs.sequelizejs.com/en/latest/api/model/](http://docs.sequelizejs.com/en/latest/api/model/) — jjjjjenkins, Apr 07 '16 at 18:36

score 2 · Answer 2 · answered Jun 27 '14 at 08:16

2

I think you would not have to query UserProject entity directly but instead use Sequelize Eager loading methods to retrieve your entities.

Your models associations should look something like this :

Project.belongsTo(Client);
Project.hasMany(User, { as: 'Workers' });
User.hasMany(Project);

and once you have all projects related to client, your finder method :

Project
     .findAll({ include: [{ model: User, as: 'Workers' })
     .success(function(users) {
         // do success things here
     }

Take a look at, http://sequelizejs.com/docs/1.7.8/models#eager-loading.

Hope it helps !

answered Jun 27 '14 at 08:16

cvng

1,822
17
23

1

To make it bulletproof add `through: 'UserProject` to both `Project.hasMany(User)` and `User.hasMany(Project)` – Mick Hansen Jun 27 '14 at 09:21
1

I made a gist of it you wanna take a look! [many-to-many](https://gist.github.com/cvng/7b30ff1c9c83302c3ef1) – cvng Jul 03 '14 at 09:33

score 0 · Accepted Answer · answered Jul 07 '14 at 20:58

Finally!!

cvng, your example helpme a lot, thanks. But I have 3 levels Client, Project, thi is my final solution, is this a good solution?

  req.tables.Client.find({
            where: { id:req.params.id },
            include: [{ model: req.tables.Project, as: 'Projects' }]
        }).success(function(client) {

            var ret ={
                    id:client.id,
                    name:client.name,
                    projects:[]
                };

            done = _.after(client.projects.length, function () {
                res.json(ret);
            });

            client.projects.forEach(function (project) {
                project.getUsers().success(function(users) {

                    var u=[]
                    users.forEach(function (user) {
                        u.push({
                            id:user.id,
                            name:user.name,
                        });
                    });

                    ret.projects.push({
                        id:project.id,
                        name:project.name,
                        users:u
                    });
                    done();

                });

            });

        });

Sequelize findall for every findall is possible?

3 Answers3