使用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。