获得pg承诺的父母+子树 [英] Get a parents + children tree with pg-promise

问题描述

我使用 pg-promise 库与 bluebird 进行相关查询。
我有两张桌子，a和b，看起来像这样：

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

其中 b.b_a 是对 a.a_id 。我想选择匹配给定 prop1 的所有条目，结果应该包含所有匹配的 a -rows加上相应的<每个 a 的code> b -rows。这应该适用于两个相关查询。两个查询都可能返回多个结果。

如果表 a 只返回一行，我可以这样做：

  function getResult（prop1）{
 return db.task（function（t）{
 return t.one（ select * from a 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（函数（b）{
 ba = a; 
}）; 
返回bs; 
}）; 
}）; 
} 
  

我还可以获得所有匹配的 b -entries为多个 a -results如下：

  function getResult（prop1）{
 return db.task（function（t）{
 return t.many（select * from a prop1 = $ 1，prop1）
 .then（function（as）{
 var queries = []; 
 as.forEach（function（a）{
 queries.push（t.any（select * from b where where b_a = $ 1，a.id））; 
}）; 
返回t.batch（查询）; //可以在这里连接查询，但是没有b行的引用属于哪一行
}）
 .then（函数（数据）{
 // data [n]包含所有匹配的b行
}）; 
} ）; 
} 
  

但如何将这两者结合在一起？

解决方案

我是的作者pg-promise 。

---

当你有2个表时：父 - > 具有1对多关系的子，并且您希望获得匹配的数组 Parent 行，每行扩展，属性 children 设置为表 Child ... <对应行的数组/ p>

有几种方法可以实现这一点，因为 pg-promise 和承诺一般都非常灵活。这是最短的版本：

  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; 
返回父级; 
}）; 
}）。then（t.batch）/ *这是以下内容的缩写：data => ; t.batch（data）* / 
}）
 .then（data => {
 / * data =完整的树* / 
}）; 
  

这就是我们在那里所做的：

首先，我们查询 Parent 项目，然后我们将每一行映射到相应的 Child 项目的查询中，然后将其行设置为 Parent 并返回它。然后我们使用方法批处理来解析<$的数组c $ c> Child 从方法返回的查询地图。

---

任务将通过如下数组解决：

  [
 {
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参考：地图，批次

更新

查看更好的答案： JOIN表作为PostgreSQL / NodeJS的结果数组。

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?

解决方案

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

API references: map, batch

UPDATE

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

这篇关于获得pg承诺的父母+子树的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！