一尘不染

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

node.js

我正在创建一个应用程序,用户可以在其中创建问题,其他人可以对其进行投票。

以下是我的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给我一个问题,并投了赞成票,这将迫使我进行服务器端操作,而我不希望这样做。

有什么办法吗?我对我想要得到的东西的推理是错误的吗?

谢谢你的时间。


阅读 337

收藏
2020-07-07

共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:mapanytaskbatch


相关问题:


而且,如果您只想使用一个查询,那么使用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文件中。请参阅查询文件

结论

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

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