Get parent + child tree with pg promise

Question

I am using the pg-promise library with bluebird to create dependent queries. I have two tables, a and b, that look like this:

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

where b.b_a is a link to a.a_id . I want to select all the records matching the given prop1 , and the result should contain all the corresponding a -rows plus the corresponding b -rows for each a . This must be done with two dependent queries. Both queries can return multiple results.

If table a returns only 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) { ba = a; }); return bs; }); }); } 

And I can also get all suitable b -entries for several 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 these two together?

Answer 1

I am the author of pg-promise .

---

If you have two tables: Parent → Child with a one-to-many relationship, and you want to get an array of matching Parent rows, each row extended with the children property is set to an array of matching rows from the Child table ...

There are several ways to do this, since the combination of pg-promise and promises is generally very flexible. Here is 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(t.batch) /* this is short for: data => t.batch(data) */ }) .then(data => { /* data = the complete tree */ }); 

This is what we do there:

First we query the Parent elements, then match each row with the query for the corresponding Child elements, which then sets its rows to Parent and returns it. Then we use the batch method to solve the array of Child queries returned by the map method.

---

The problem will be solved using such an array:

 [ { "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 links: map , batch

UPDATE

See the best answer to this question: JOIN table as an array of results with PostgreSQL / NodeJS .