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

发布时间：2020-12-13 16:17:23 所属栏目：百科来源：网络整理

导读：我正在创建一个应用程序,用户可以在其中创建问题,而其他人则可以对其进行upvote / downvote. 以下是我的sql架构的一部分： CREATE TABLE "questions" ( id SERIAL,content VARCHAR(511) NOT NULL,created_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(

我正在创建一个应用程序,用户可以在其中创建问题,而其他人则可以对其进行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给了我一个加上投票的问题,并迫使我做服务器端操作,我不愿意这样做.

有没有办法做到这一点？我的理由是关于我想要获得的错误吗？

谢谢你的时间.

这很容易用 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);
    });

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',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文件中保留这些复杂的查询.见Query Files.

结论

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

>单查询方法更快,但有些难以阅读或扩展,相当冗长
>多查询方法更易于理解和扩展,但由于执行的查询的动态数量,它对性能而言并不好.

UPDATE

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

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!