Best way to query a Many to Many Relationship usin

Question

For example I want to get the user info, emails and it's roles from db and create an object like :

{
  "id": 1,
  "firstname": "John",
  "lastname": "Johnny",
  "emails": [
    {
      "type": "work",
      "email": "[e-mail]"
    },
    {
      "type": "personal",
      "email": "[e-mail]"
    }
  ],
  "roles": [
    {
      "role": "ADM",
      "title": "Admin"
    },
    {
      "role": "PUB",
      "title": "Publisher"
    }
  ]
}

There are three tables I need to query:

• Users table has id, firstname,lastname.
• Emails table has type, email,user_id.
• Roles table has role, title,user_id.

Based on the pg-promise's wiki I am almost sure it has to be done using Tasks but not sure how would you chain them.

UPDATE In my actual project I had to insert a product and use the generated id to insert attributes. Adding my code here in case you have a similar situation:

//Insert a new product with attribites as key value pairs
post_product_with_attr: function(args) {
    return db.task(function(t) {
        return t.one(sql.post_new_product, args)
            .then(function(dt) {
                var queries = [];
                Object.keys(args).forEach(function(key) {
                    queries.push(t.one(sql.post_attr_one, { Id: dt.id, key: key, value: args[key] }));
                 });
                return queries.length ? t.batch(queries) : [dt];
            });
    });
}

Answer 1

What you are describing isn't a Many-to-Many relationship, it is 2 One-To-Many relationships.

Based on the pg-promise's wiki I am almost sure it has to be done using Tasks but not sure how would you chain them.

In your example there is no need to chain anything. One chains promises when the result of one needs to be used as the criteria for the next one, because this is how promises work, not just queries.

Because of that, you can execute all 3 queries in parallel, as a batch.

The example below uses pg-promise with Bluebird as the promise library:

function getUserInfo(userId) {
    return db.task(t=>t.batch([
            t.one('SELECT id, firstname, lastname FROM Users WHERE id = $1', userId),
            t.any('SELECT type, email FROM Emails WHERE user_id = $1', userId),
            t.any('SELECT role, title FROM Roles WHERE user_id = $1', userId)
        ]))
        .spread((user, emails, roles)=> {
            user.emails = emails;
            user.roles = roles;
            return user;
        })
}

Usage example:

getUserInfo(123)
    .then(info=> {
        // info = object as described in the question;
    })
    .catch(error=> {
        // error;
    });