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

Question

我正在创建一个用户可以创建问题的应用程序，而其他人可以对其进行upvote / downvote。

以下是我的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（）），但它只给了我一些值。一个JOIN给了我一个加上投票的问题，并迫使我做服务器端操作，我不愿意这样做。

有没有办法做到这一点？关于我想弄错的是什么原因？

谢谢你的时间。

Answer 1

pg-promise很容易做到这一点：function buildTree(t) {    return t.map('SELECT * FROM questions', [], q => {        return t.any('SELECT id, value FROM votes WHERE question_id = $1', q.id)            .then(votes => {                q.votes = votes;                return q;            });    }).then(t.batch); // settles the array of generated promises}db.task(buildTree)    .then(data => {        console.log(data); // your data tree    })    .catch(error => {        console.log(error);    });如果您只想使用单个查询，那么使用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`;db.map(query, [], a => a.json)    .then(data => {        console.log(data); // your data tree    })    .catch(error => {        console.log(error);    });而且你肯定会希望在外部SQL文件中保留这些复杂的查询。请参阅查询文件。结论上述两种方法之间的选择应基于您的应用程序的性能要求：单查询方法更快，但有些难以阅读或扩展，相当冗长多查询方法更容易理解和扩展，但由于执行的查询的动态数量，它对性能不是很好。UPDATE以下相关答案通过连接子查询提供了更多选项，这将提供更好的性能：将嵌套循环查询组合到父结果pg-promise。