Get a parents + children tree with pg-promise

Question

I use the pg-promise library with bluebird for making dependent queries. I have two tables, a and b, looking like this:

|   a   |     |   b   |  
|-------|     |-------|
| a_id  |     | b_id  |
| prop1 |     | prop2 |
              |  b_a  |

where b.b_a is a reference to a.a_id. I want to select all entries matching a given prop1 and the result should contain all matching a-rows plus the corresponding b-rows for each a. This should be doable with two dependent queries. Both queries may return multiple results.

If table a only returns one row I can do this:

function getResult(prop1) {
    return db.task(function (t) {
        return t.one("select * from a where prop1=$1", prop1)
            .then(function (a) {
                return t.batch([a, t.any("select * from b where b_a=$1", a.a_id)]);
            })
            .then(function (data) {
                var a = data[0];
                var bs = data[1];
                bs.forEach(function (b) {
                    b.a = a;
                });
                return bs;
            });
    });
}

And I'm also able to get all matching b-entries for multiple a-results like this:

function getResult(prop1) {
    return db.task(function (t) {
        return t.many("select * from a where prop1=$1", prop1)
            .then(function (as) {
                var queries = [];
                as.forEach(function (a) {
                    queries.push(t.any("select * from b where b_a=$1", a.id));
                });
                return t.batch(queries); // could concat queries with as here, but there wouldn't be a reference which b row belongs to which a row
            })
            .then(function (data) {
                // data[n] contains all matching b rows
            });
    });
}

But how to bring those two together?

Canonical

They're in the same order, right? So just access as[n] together with data[n]. Have a look here on how to access as. – Dzerzhinsk

Thanks for the link, that might help. But there is a one to many relationship between a and b, so the indizes are not helpful here.. – Canonical

What's the return value of batch? Isn't data a two-dimensional array of some kind? – Dzerzhinsk

Just seen that it's multi-dimensional a second before I read your comment.. thanks a lot for that, could have seen that earlier! – Canonical

where a.a_b is a reference - there is no a_b in table a. – Timepiece

the result should contain all matching a-rows plus the corresponding b-rows for each a. do you want a single object with the tree or an array of some sort? Not clear exactly what type of output you want. – Timepiece

@Canonical I have posted an answer for you below ;) – Timepiece

Answer 1

I am the author of pg-promise.

---

When you have 2 tables: Parent -> Child with 1-to-many relationship, and you want to get an array of matching Parent rows, each row extended with property children set to an array of the corresponding rows from table Child ...

There are several ways to accomplish this, as the combination of pg-promise and promises in general is very flexible. Here's the shortest version:

db.task(t => {
    return t.map('SELECT * FROM Parent WHERE prop1 = $1', [prop1], parent => {
        return t.any('SELECT * FROM Child WHERE parentId = $1', parent.id)
            .then(children => {
                parent.children = children;
                return parent;
            });
    }).then(a => t.batch(a))
})
    .then(data => {
        /* data = the complete tree */
    });

This is what we do there:

First, we query for Parent items, then we map each row into a query for the corresponding Child items, which then sets its rows into the Parent and returns it. Then we use method batch to resolve the array of Child queries returned from method map.

UPDATE for ES7

Here's the same as above, but using ES7 async/await syntax:

await db.task(async t => {
    const parents = await t.any('SELECT * FROM Parent WHERE prop1 = $1', [prop1]);
    for(const p of parents) {
        p.children = await t.any('SELECT * FROM Child WHERE parentId = $1', [p.id]);
    }
    return parents;
});
// the task resolves with the correct data tree

---

The task will resolve with an array like this:

[
    {
        "parent1-prop1", "parent1-prop2",
        "children": [
            {"child1-prop1", "child1-prop2"},
            {"child2-prop1", "child2-prop2"}
        ]
    },
    {
        "parent2-prop1", "parent2-prop2",
        "children": [
            {"child3-prop1", "child3-prop2"},
            {"child4-prop1", "child4-prop2"}
        ]
    }    
]

API references: map, batch

UPDATE

See a better answer to this: JOIN table as array of results with PostgreSQL/NodeJS.

Timepiece

Thanks a lot, map is very helpful here - I somehow didn't find that myself. – Canonical

@Canonical map is only a convenience method that makes your code shorter, but not strictly required to implement your task ;) – Timepiece

@Canonical Updated with a link to a better/fuller answer ;) – Timepiece

Added ES7 example. – Timepiece