Best way to query many-to-many relationships with pg-prom

Question

For example, I want to get user information, emails and his roles from db and create an object such as:

{ "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" } ] } 

I need to query three tables:

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

Based on the pg-promise wiki, I'm pretty sure it needs to be done using Tasks , but don't know how you would tie them together.

UPDATE In my actual project, I had to insert the product and use the generated identifier to insert the attributes. Adding my code here if 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 is not a many-to-many relationship, it is 2 one-to-many relationships.

Based on the pg-promise wiki, I'm pretty sure it needs to be done using Tasks , but don't know how you would tie them together.

In your example, there is no need to cling to anything. One chain of promises, when the result should be used as criteria for the next, because it works promises, not just queries.

Because of this, you can execute all 3 requests in parallel, like a package.

The following example uses pg-promise with Bluebird as a 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; });