Teka Teka - 2 months ago 14
Node.js Question

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.

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.

Answer

This is easy to do with 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);
}

db.task(buildTree)
    .then(data=> {
        console.log(data); // your data tree
    })
    .catch(error=> {
        console.log(error);
    });

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:

var 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);
    });

Conclusion

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

  • The single-query approach is faster, but it is somewhat difficult to read or extend, as it is quite verbose
  • The multi-query approach is easier to understand and to extend, but it is not the best for performance, due to multiple queries executed.
Comments