sql - 使用 PostgreSQL/NodeJS 获取 JOIN 表作为结果数组

Question

我正在创建一个应用程序，用户可以在其中提出问题，其他人可以投票/反对。

以下是我的 sql 架构的一部分:

CREATE TABLE "questions" (
  id            SERIAL,
  content       VARCHAR(511) NOT NULL,
  created_at    TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
  CONSTRAINT    pk_question PRIMARY KEY (id)
);

CREATE TABLE "votes" (
  id            SERIAL,
  value         INT,
  question_id   INT NOT NULL,
  CONSTRAINT    pk_vote PRIMARY KEY (id),
  CONSTRAINT    fk_question_votes FOREIGN KEY (question_id) REFERENCES questions (id) MATCH SIMPLE ON UPDATE CASCADE ON DELETE CASCADE
);

我想要的是 Postgres 给我每个问题的投票，就像这样:

[{ // a question
  id: 1,
  content: 'huh?',
  votes: [{ // a vote
    id: 1,
    value: 1
  }, { // another vote
    id: 2,
    value: -1
  }]
}, { /*another question with votes*/ }]

我查看了聚合函数(如 array_agg())，但它只给了我值。 A JOIN 给了我一个投票加入的问题，并会迫使我进行服务器端操作，我不希望这样做。

有没有办法做到这一点？我对我想要获得什么的推理是错误的吗？

感谢您的宝贵时间。

Answer 1

使用 pg-promise 很容易做到这一点:

function buildTree(t) {
    const v = q => t.any('SELECT id, value FROM votes WHERE question_id = $1', q.id)
        .then(votes => {
            q.votes = votes;
            return q;
        });

    return t.map('SELECT * FROM questions', undefined, v).then(a => t.batch(a));
}

db.task(buildTree)
    .then(data => {
        console.log(data); // your data tree
    })
    .catch(error => {
        console.log(error);
    });

同上，但使用 ES7 async/await 语法:

await db.task(async t => {
    const questions = await t.any('SELECT * FROM questions');
    for(const q of questions) {
        q.votes = await t.any('SELECT id, value FROM votes WHERE question_id = $1', [q.id]);
    }
    return questions;
});
// method "task" resolves with the correct data tree

API:map , any , task , batch

---

相关问题:

• Get a parents + children tree with pg-promise
• Conditional task with pg-promise

---

如果您只想使用单个查询，那么使用 PostgreSQL 9.4 及更高版本的语法，您可以执行以下操作:

SELECT json_build_object('id', q.id, 'content', q.content, 'votes',
    (SELECT json_agg(json_build_object('id', v.id, 'value', v.value))
     FROM votes v WHERE q.id = v.question_id))
FROM questions q

然后是你的 pg-promise例如:

const query =
    `SELECT json_build_object('id', q.id, 'content', q.content, 'votes',
        (SELECT json_agg(json_build_object('id', v.id, 'value', v.value))
         FROM votes v WHERE q.id = v.question_id)) json
    FROM questions q`;
    
const data = await db.map(query, [], a => a.json);

而且您肯定希望将这些复杂的查询保存在外部 SQL 文件中。见 Query Files .

结论

上述两种方法之间的选择应基于应用程序的性能要求:

• 单查询方法更快，但有点难以阅读或扩展，相当冗长
• 多查询方法更易于理解和扩展，但由于执行的查询数量是动态的，因此性能不佳。

UPDATE-1

以下相关答案通过连接子查询提供了更多选项，这将大大提高性能:Combine nested loop queries to parent result pg-promise .

UPDATE-2

添加了另一个示例，使用 ES7 async/await 方法。