Conditional task with pg-promise

Question

I am trying to simply read a value from a table and based on the return value call for additional queries and return the combined results.

let's take a simple example: table Users has id, name and emailid and let's say if emailid is not null we want to call the email table and return a results like { id:[id], name:[name], email:[email]}.

Weigand

Answer 1

Using the latest syntax supported by pg-promise:

db.task(t => {
    return t.map('SELECT * FROM Users', [], user => {
        return user.emailid ?
            t.one('SELECT * FROM Emails WHERE id = $1', user.emailid, e=> {
                user.email = e.email;
                return user;
            }) : user;
    }).then(t.batch);
})
    .then(data => {
        // success
    })
    .catch(error => {
        // error
    });

See the API: Database.map, Database.one.

See also a related question: Get a parents + children tree with pg-promise.

---

NOTE: A better approach would use a single query with INNER JOIN.

Bim

I also agree, in the above case, left join is the way to go but my actual code is more complicated. Thanks! – Weigand

@Weigand I've realized I made one stupid mistake there, just corrected it, replacing : null with : user. Otherwise it was replacing each user who didn't have email with a null :) – Bim

Just as I came back to suggest a correction , I saw this comment! You are awesome man. Thanks ! – Weigand