I have a scenario where I have three tables in a PostgreSQL database, users, orgs, and users_orgs, which links the first two. I use Express and pg to handle the DB calls. Now, if I want to, say, attach a list of org records to a user record, I want to perform one query for the user record, one for all of the users_orgs associated with that user, and then one for each of those records to get the orgs.

When I do this, I end up with something like

const user_query = `SELECT * FROM users WHERE id=${id}`;
pg.query(user_query)
  .then((user_result) => {
    const users = user_result.rows;
    users.map((user) => {
      user.orgs = [];
      const users_orgs_query = `SELECT org_id FROM users_orgs WHERE user_id = '${user.id}'`;
      pg.query(users_orgs_query)
        .then((users_orgs_result) => {
           return new Promise((res, rej) => {
             const users_orgs = users_orgs_result.rows;
             let c = 0;
             users_orgs.map((user_org) => {
               const orgs_query = `SELECT * FROM orgs WHERE id = '${user_org.org_id}'`;
               pg.query(orgs_query)
                 .then((r) => {
                   const orgs = r.rows;
                   user.orgs.push(orgs[0]);
                   c  = 1;
                   if (c >= users_orgs.length) {
                     res(user);
                   }
                 });
             });
          });
        })
        .then((u) => {
          res.status(200).json(u);
        });
    });

Now, this works, but I feel confident that counting into my map is not a good idea. Clearly, I could replace the inner map with a for loop and just count and resolve the promise that way as well, but that feels like something I would do in C (which is where I normally work) and that feels like cheating.

However, I need the resolve to happen after the last element maps because otherwise, I will respond to the request before adding all the orgs to the user. Surely this is not uncommon, but I feel like I am not seeing anything related when I search, which leads me to believe that I'm searching poorly and/or thinking about this all wrong.

There may even be a SQL query-based solution to this type of thing, and that's great, but I would still be curious if there is a way to wait for a loop of nested promises to resolve in an elegant manner.

To make it clear, the actual question is, is there a way to know that all of the inner promises have been resolved without having to actually count them all, as I do here?

CodePudding user response：

Async version, btw your code is a little complex, so maybe not work. If not working, please comment:

const user_query = `SELECT * FROM users WHERE id=${id}`;
const user_result = await pg.query(user_query);
const users = result.rows;
for (let i = 0; i < users.length; i  ) {
    const user = users[i];
    user.orgs = [];
    const users_orgs_query = `SELECT org_id FROM users_orgs WHERE user_id = '${user.id}'`;
    const users_orgs_result = await pg.query(users_orgs_query);
    const users_orgs = users_orgs_result.rows;
    for (let j = 0; j < users_orgs.length; j  ) {
        const user_org = users_orgs[j];
        const orgs_query = `SELECT * FROM orgs WHERE id = '${user_org.org_id}'`;
        const r = await pg.query(orgs_query);
        const orgs = r.rows;
        user.orgs.push(orgs[0]);
        if (c == users_orgs.length - 1) {
            res.status(200).json(u);
        }
    }
}

CodePudding user response：

You can wait for all the promises to finish by using Promise.all. This function accepts an array of promises and returns a Promise by itself, which will resolve when all given promises have successfully been resolved.

Use this is in combination map method of the array to return an array of promises.

async function getUsersWithOrgs(id) {
  const user_query = `SELECT * FROM users WHERE id=${id}`;
  const { rows: users } = await pg.query(user_query)

  return Promise.all(users.map(async user => {
    const users_orgs_query = `SELECT org_id FROM users_orgs WHERE user_id = '${user.id}'`;
    const { rows: users_orgs } =  await pg.query(users_orgs_query);

    const orgs = await Promise.all(users_orgs.map(async user_org => {
      const orgs_query = `SELECT * FROM orgs WHERE id = '${user_org.org_id}'`;
      const { rows: orgs } = await pg.query(orgs_query);
      return orgs[0];
    }));
    
    return {
      ...user,
      orgs
    };
  }));
}

getUsersWithOrgs(id).then(users => {
  res.status(200).json(users);
});

CodePudding user response：

You need to use async, await and for the inner promises you can use Promise.all

awaiting individually query will be un-optimal.

You can structure your code like (taking the innermost query as an example):

const orgs_query = `SELECT * FROM orgs WHERE id = '${user_org.org_id}'`;
await Promise.all(user_orgs.map((user_org) => {
    pg.query(orgs_query)
        .then((r) => {
           const orgs = r.rows;
           user.orgs.push(orgs[0]);
        });
}));

// Return the response as you like from the users object

The pq.query returns a Promise from the mapped function. The outer Promise.all will collect these promises and await till all of them return.

This way you don't need to nest.