pg-promise task and map with multiple same level nested queries

Question

I am using node and pg-promise to create a basic rest API and am having some issue querying all data for a particular user. Below is what the data returned should look like. Address, Phone Number, and Skills all live in separate tables. I am having no issue retrieving addresses or phone numbers its just skills I can't seem to get. Not quite sure how to have multiple queries after the main query that gets the user to get all these other fields, please see the attached code for reference and I will be happy to answer any questions.

{
"user_id": 1,
"first_name": "Eugene",
"last_name": "Hanson",
"display_name": "Eugene Hanson",
"email": "ehanson0@typepad.com",
"hash": "88a6aa27235d2e39dd9cb854cc246487147050f265578a3e1aee35be5db218ef",
"privilege_id": 14,
"seniority": 1,
"birthday": "19-11-1940 00:00:00.0",
"shift_count_total": 587,
"shift_count_year": 62,
"address_id": 1,
"street": "92 Schmedeman Lane",
"city": "Fort Smith",
"state": "AR",
"zip": 72905,
"phone_numbers": [
  {
    "phone_number": "62-(705)636-2916",
    "name": "PRIMARY"
  }
],
"skills": [
    "Head Audio",
    "Head Video",
    "Head Electrician",
    "Carpenter",
    "rigger"
    ]
}

    function getAllUsers() {
    // console.time("answer time")
    var deferred = Q.defer();
    db.task(t => {
        return t.map('SELECT * \
                        FROM users \
                        JOIN addresses \
                        ON users.address_id = addresses.address_id',[], user => {
            var user_id = user.user_id;
            // console.log(user_id)
            console.time("answer time")
            return t.manyOrNone('SELECT phone_numbers.phone_number, phone_types.name \
                                    FROM users \
                                    JOIN users_phone_numbers \
                                    ON users.user_id = users_phone_numbers.user_id \
                                    JOIN phone_numbers \
                                    ON users_phone_numbers.phone_id = phone_numbers.phone_id \
                                    JOIN phone_types \
                                    ON phone_numbers.phone_type_id = phone_types.phone_type_id \
                                    WHERE users.user_id = $1', user.user_id)
                                    .then(phone_numbers=> {
                                        // logger.log('info', phone_numbers)
                                        user.phone_numbers = phone_numbers;
                                        return user;
                                    })
        }).then(t.batch);
    })
    .then(data => {
        // console.log(data)
        console.timeEnd("answer time");
        var response = {code: "200", 
                        message: "", 
                        payload: data};
        deferred.resolve(response);
    })
    .catch(error => {
    var response = {code: error.code, 
                    message: error.message, 
                    payload: ""};
    logger.log('error', error)
    deferred.reject(response)
});

And what is the issue? What are you getting instead? Where is your `.catch` handler? You need to finish your question properly, before it can be answered ;) — vitaly-t, Apr 04 '17 at 08:55
Edited the original post to include the `.catch` that was accidentally left out in my copying and pasting. Sorry that my question was not clear, what I am trying to do is go through all users then subsequently get all of their associated data from various other tables then merge it back into the user record and send it back to the client. I can currently only do this for phone numbers with the map not sure how I would add another query to also be used in the map to say get skills. Any advice is welcome and feel free to let me know if I need to clarify more. — seanpdiaz, Apr 04 '17 at 09:21
I would explore getting more sophisticated with my SQL; using some LEFT JOINs and .reduce() in the code. Might also be able to use postgres' array_agg https://lorenstewart.me/2017/12/04/postgresqls-array_agg-function/ which looks like it can help you de-normalize relational data in a query. — grantwparks, Oct 03 '19 at 16:08

score 3 · Accepted Answer · edited Jun 20 '20 at 09:12

I'm the author of pg-promise.

Simplified version of your function would be:

function getAllUsers() {
    return db.task(t => {
        return t.map('SELECT * FROM users', [], user => {
            return t.batch([
                t.any('SELECT * FROM phones'), // plus formatting params
                t.any('SELECT * FROM skills'), // plus formatting params
            ])
                .then(data => {
                    user.phones = data[0];
                    user.skills = data[1];
                    return user;
                });
        }).then(t.batch);
    });
}

getAllUsers()
    .then(data => {
        // data tree
    })
    .catch(error => {
        // error
    });

And if you are using bluebird as the promise library, then you can replace this code:

.then(data => {
    user.phones = data[0];
    user.skills = data[1];
    return user;
});

with this one:

.spread((phones, skills) => {
    user.phones = phones;
    user.skills = skills;
    return user;
});

And do not use things like var deferred = Q.defer();, it is not needed there. The library is already promise-based.

For a high-performance alternative see: get JOIN table as array of results with PostgreSQL/NodeJS.

**Thank You** that is exactly what I needed, I was hunting all over the internet on how to put these pieces together but couldn't find a clear idea and you were able to shed some light on it. — seanpdiaz, Apr 04 '17 at 10:09
Thanks for pointing me in the right direction on how to make my call more performant, I was able to use the link you provided and get my response down from 12,000 ms to under 500ms. **Thank You** again. — seanpdiaz, Apr 04 '17 at 23:59

pg-promise task and map with multiple same level nested queries

1 Answers1