CODEWITHSUNDEEP
javascriptnode.jspostgresqlpg-promise
seanpdiaz
seanpdiaz

Reputation: 33

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

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

Upvotes: 3

Views: 1541

Answers (1)

vitaly-t
vitaly-t

Reputation: 25840

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.

Upvotes: 3

Related Questions