Skip to content
Advertisement

How to aggregate upvote and post tables together in postgres?

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

users.sql

posts.sql

Basically I want to make my response look something like this:

post.json

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.

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.

User contributions licensed under: CC BY-SA
9 People found this is helpful
Advertisement