I'm creating an app where users are able to create questions, and others can upvote/downvote them.
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
);
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*/ }]
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?
Thanks for your time.
This is easy to do with 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', undefined, v).then(a => t.batch(a));
}
db.task(buildTree)
.then(data => {
console.log(data); // your data tree
})
.catch(error => {
console.log(error);
});
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
API: map, any, task, batch
Related questions:
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
And then your pg-promise example would be:
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);
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
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
Another example added, using ES7 async
/await
approach.
Please think simple way, May be I am right, I use knex js
let allpost = knex
.select([
'questions.id',
'question.content',
knex.raw('json_agg(v.*) as votes')
])
.from('questions')
.leftJoin('votes as v', 'questions.id', 'v.question_id')
.groupBy('questions.id');
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With