使用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（）），但它仅提供了值。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', [], v).then(t.batch);
}

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

API：map，any，task，batch

相关问题：

• 用pg-promise获得父母和孩子的树
• 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`;

db.map(query, [], a => a.json)
    .then(data => {
        console.log(data); // your data tree
    })
    .catch(error => {
        console.log(error);
    });

而且，您肯定希望将这样复杂的查询保留在外部SQL文件中。请参阅查询文件。

结论

上面介绍的两种方法之间的选择应基于您的应用程序的性能要求：

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