justasking justasking - 1 month ago 18
Node.js Question

Nested query object mapping with pg-promise

I'm going through an example from pg-promise for method map:

// Build a list of active users, each with the list of user events:
db.task(t => {
return t.map('SELECT id FROM Users WHERE status = $1', ['active'], user => {
return t.any('SELECT * FROM Events WHERE userId = $1', user.id)
.then(events=> {
user.events = events;
return user;
});
}).then(t.batch);
})
.then(data => {
// success
})
.catch(error => {
// error
});


Let's say the
Event
entity has a one to many relationship with e.g.
Cars
, and I want to list all the
cars
connected to each
event
, how can I use the map function when the object I want is more than one level deep?

The result I want could look something like this:

[{
//This is a user
id: 2,
first_name: "John",
last_name: "Doe",
events: [{
id: 4,
type: 'type',
cars: [{
id: 4,
brand: 'bmw'
}]
}]
}]

Answer

I'm the author of pg-promise.


db.task(t => {
    return t.map('SELECT id FROM Users WHERE status = $1', ['active'], user => {
        return t.map('SELECT * FROM Events WHERE userId = $1', user.id, event=> {
            return t.any('SELECT * FROM Cars WHERE carId = $1', event.carId)
                .then(cars=> {
                    event.cars = cars;
                    return event;
                });
        })
            .then(t.batch) // settles internal array of promises
            .then(events=> {
                user.events = events;
                return user;
            });
    }).then(t.batch); // settles external array of promises
})
    .then(users => {
        // users = an object tree of users->events->cars
    })
    .catch(error => {
        // error
    });

There is also a faster, single-query approach that can be found here: get JOIN table as array of results with PostgreSQL/NodeJS