Nested query object mapping with pg-promise

Question

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'
        }]
    }]
}]

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

I'm the author of pg-promise.

function getUsers(t) {
    return t.map('SELECT * 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 eventId = $1', event.id)
                .then(cars => {
                    event.cars = cars;
                    return event;
                });
        })
            .then(t.batch) // settles array of requests for Cars (for each event)
            .then(events => {
                user.events = events;
                return user;
            });
    }).then(t.batch); // settles array of requests for Events (for each user)
}

And then using it:

db.task(getUsers)
    .then(users => {
        // users = an object tree of users->events->cars
    })
    .catch(error => {
        // error
    });

Method map simplifies mapping retrieved rows into something else, and since we map them into promises, those need to be settled, for which we use method batch. We do it for each internal array of requests for cars, and then on the top level - to settle the array of requests for events.

UPDATE

It may be easier to read and maintain, if you do the tree logic upside down:

function getUsers(t) {
    const getCars = eventId => t.any('SELECT * FROM Cars WHERE eventId = $1', eventId);

    const getEvents = userId => t.map('SELECT * FROM Events WHERE userId = $1', userId, event => {
        return getCars(event.id)
            .then(cars => {
                event.cars = cars;
                return event;
            });
    }).then(t.batch);

    return t.map('SELECT * FROM Users WHERE status = $1', ['active'], user => {
        return getEvents(user.id)
            .then(events => {
                user.events = events;
                return user;
            });
    }).then(t.batch);
}

There is also a faster, single-query approach that can be found here:

get JOIN table as array of results with PostgreSQL/NodeJS

Nested query object mapping with pg-promise

1 Answers1