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

本文介绍了使用PostgreSQL/NodeJS获取JOIN表作为结果数组的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

我正在创建一个应用程序，用户可以在其中创建问题，其他人可以对其进行投票/降级投票.

I'm creating an app where users are able to create questions, and others can upvote/downvote them.

以下是我的sql模式的一部分:

The following is a part of my sql schema:

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给我每个问题一个投票，就像这样:

What I would like to have is Postgres giving me each question with an array of votes, like that:

[{ // 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给我一个问题，并投了赞成票，这将迫使我进行服务器端操作，而我不希望这样做.

I looked at aggregate functions (like array_agg()) but it gave me only the values. A JOIN gave me a question joined with a vote, and would force me to do server side operations, which I would prefer not to.

有没有办法做到这一点?我对想要得到的东西有错误的看法吗?

Is there any way to do that? Is my reasoning regarding what I want to obtain wrong?

感谢您的时间.

推荐答案

使用 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:地图，任何，任务，批次

相关问题:

• 获得具有pg-promise的父母和孩子的树
• 具有pg-promise的条件任务

• Get a parents + children tree with pg-promise
• Conditional task with pg-promise

如果只想使用一个查询，那么使用PostgreSQL 9.4和更高版本的语法，您可以执行以下操作:

And if you want to use just a single query, then using PostgreSQL 9.4 and later syntax you can do the following:

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文件中.参见查询文件.

And you definitely will want to keep such complex queries in external SQL files. See Query Files.

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

The choice between the two approaches presented above should be based on the performance requirements of your application:

• 单查询方法速度更快，但是相当冗长，但难以读取或扩展
• 多查询方法更易于理解和扩展，但由于执行的查询数量动态，因此对性能的影响不大.

更新

以下相关答案通过串联子查询提供了更多选项，这将大大提高性能:将嵌套循环查询合并为父结果pg-promise .

The following related answer offers more options, by concatenating child queries, which will give a much improved performance: Combine nested loop queries to parent result pg-promise.