Parham Parham - 6 months ago 107
Node.js Question

Best way to query a Many to Many Relationship using pg-promise

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": "work@work.com"
},
{
"type": "personal",
"email": "personal@personal.com"
}
],
"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 > 0) ? t.batch(queries) : [dt];
});
});
}

Answer

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