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

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": "[email protected]"
    },
    {
      "type": "personal",
      "email": "[email protected]"
    }
  ],
  "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];
            });
    });
}

Leggy

You chain queries when parameters of one query are the result of the the previous query, which isn't the case in your example. Your example has no dependency between query results. No chaining is needed. – Malefaction

@vitaly-t, Yes, I made a mistake on the question. My actual case is that I have the email and need to get the user_id from the email table first. – Leggy

@vitaly-t, Does anything look out of place with the Update I just added, It adds the product and returns the id but using debugger I am getting an asynchronous error of Loose request outside an expired connection. at pg-promise/lib/query.js line 183 reject(errMsg); – Leggy

yeah, (queries > 0) is an invalid piece of code. Should be (queries.length > 0), or simply: return queries.length? t.batch(queries) : [dt];. That's why it fails there, as you never execute t.batch(queries). – Malefaction

@vitaly-t, That was it! Thanks! I am not even sure how it got like that in the first place. lol – Leggy

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 either in parallel, or independently, as shown in the example below:

function getUserInfo(userId) {
    return db.task(async t => {
        const user = await t.one('SELECT id, firstname, lastname FROM Users WHERE id = $1', userId);
        const emails = await t.any('SELECT type, email FROM emails WHERE user_id = $1', userId),
        const roles = await t.any('SELECT role, title FROM roles WHERE user_id = $1', userId);

        user.emails = emails;
        user.roles = roles;

        return user;
});

Usage example:

const user = await getUserInfo(123); //=> user object with all details

Note that it is much more efficient to do this via existing PostgreSQL JSON functions, which let you execute just one query then.

Malefaction

Thanks! Could you let me know how to do it if I have the user name instead and have to query the user table first to get the id out of it ? – Leggy

@Leggy is this of any help? - github.com/vitaly-t/pg-promise/blob/master/examples/… – Malefaction

yes, I think that should work. Thanks. I will try it as soon as I get a chance and will update. – Leggy

Hey, Yeah, it did! Thanks! I will post a version of my code shortly. – Leggy

Updated the answer, to use the new async syntax, for simplicity. – Malefaction