Krxldfx - 1 year ago 89

Javascript Question

I use the

`pg-promise`

`bluebird`

I have two tables, a and b, looking like this:

`| a | | b |`

|-------| |-------|

| a_id | | b_id |

| prop1 | | prop2 |

| b_a |

where

`b.b_a`

`a.a_id`

`prop1`

`a`

`b`

`a`

If table

`a`

`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`

`a`

`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?

Answer Source

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(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 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.

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"}
]
}
]
```