使用 PostgreSQL/NodeJS 获取 JOIN 表作为结果数组 [英] get JOIN table as array of results with PostgreSQL/NodeJS
问题描述
我正在创建一个应用程序，用户可以在其中创建问题，其他人可以对它们进行投票/否决.
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())，但它只给了我值.A 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 承诺:
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', undefined, v).then(a => t.batch(a));
}
db.task(buildTree)
.then(data => {
console.log(data); // your data tree
})
.catch(error => {
console.log(error);
});
同上，但使用 ES7 async/await 语法:
The same as above, but using ES7 async/await syntax:
await db.task(async t => {
const questions = await t.any('SELECT * FROM questions');
for(const q of questions) {
q.votes = await t.any('SELECT id, value FROM votes WHERE question_id = $1', [q.id]);
}
return questions;
});
// method "task" resolves with the correct data tree
相关问题:
如果您只想使用单个查询，那么使用 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`;
const data = await db.map(query, [], a => a.json);
而且您肯定希望将如此复杂的查询保留在外部 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:
- 单查询方法更快，但有点难以阅读或扩展，相当冗长
- 多查询方法更易于理解和扩展，但由于执行的查询数量是动态的，因此性能不佳.
UPDATE-1
以下相关答案通过连接子查询提供了更多选项，这将大大提高性能:将嵌套循环查询组合到父结果 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.
UPDATE-2
添加了另一个示例，使用 ES7 async/await 方法.
Another example added, using ES7 async/await approach.
这篇关于使用 PostgreSQL/NodeJS 获取 JOIN 表作为结果数组的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
