I’m looking for a way to aggregate posts and the upvotes related to that post together using PostgreSQL. I want to make it similar to reddit’s upvote systems, where upvotes come already fetched and don’t need to be loaded on client side.
I have made these simple tables.
upvotes.sql
CREATE TABLE upvotes ( id BIGSERIAL NOT NULL PRIMARY KEY, post_id BIGINT NOT NULL REFERENCES posts(id) user_id BIGINT NOT NULL REFERENCES users(id) );
users.sql
CREATE TABLE users ( id BIGSERIAL NOT NULL PRIMARY KEY, name VARCHAR NOT NULL );
posts.sql
CREATE TABLE posts ( id BIGSERIAL NOT NULL PRIMARY KEY, content VARCHAR NOT NULL )
Basically I want to make my response look something like this:
post.json
{ id: 1234, content: "foo", upvotes: 1234 }
Is there a way for doing this with a single query? I feel like this is a simple question but don’t really know how to make it work…
Advertisement
Answer
Here it is.
select to_jsonb(t.*) from ( select p.id, p.content, count(*) upvotes_count from posts p inner join upvotes uv on p.id = uv.post_id group by p.id, p.contents ) t -- where id = 1234;
Please note that if posts.id
is primary key then p.contents
is redundant in the group by
clause. We do not actually pay attention who are the users that upvoted the post but only how many they are. So table users
is not used in the query.