Get all posts with sum of votes and if current user voted each post

If have the following two PostgreSQL tables:

Post table:

postid | title | author | created

Vote table:

postid | username | vote

where vote is equal to 1 if the user voted the post up, 0 if the user did not vote and -1 if the user voted the post down.

I want to receive now for every post its title, author, created date, sum of all votes and the vote of the current logged in user. I wrote a query to receive everything except the vote of the current user like this:

SELECT post.postID as postID, post.title as title, post.author as author,
       COALESCE(sum(votes.vote), 0) as voteCount, post.created as created
       FROM post LEFT JOIN votes ON post.postID = votes.postID 
       GROUP BY post.postID ORDER BY voteCount DESC

I tried to fetch the current userVote by running a subquery like

(SELECT vote FROM votes WHERE postID = post.postID AND username = :username) as userVote 

However it does not seem to work and I am unable to figure out why and how to fix it. Any help would be very appreciated 🙂

Answer

You are almost there with your query. Just use filter clause to get current user’s vote count

SELECT 
    post.postID as postID, 
    post.title as title, 
    post.author as author,
    post.created as created,
    COALESCE(sum(votes.vote), 0) as voteCount, 
    COALESCE(sum(votes.vote) filter (where votes.username= 'username'), 0) as userVote  -- in '' just provide username for current login user
FROM post 
LEFT JOIN votes ON post.postID = votes.postID 
GROUP BY 1,2,3,4 
ORDER BY 5 DESC

Another way is by using one more left join like below:

SELECT 
    p.postID as postID, 
    p.title as title, 
    p.author as author,
    p.created as created,
    COALESCE(sum(v1.vote), 0) as voteCount, 
    COALESCE(v2.vote , 0) as userVote  -- in '' just provide username for current login user
FROM post p
LEFT JOIN votes v1 ON p.postID = v1.postID 
LEFT JOIN votes v2 on p.postID = v2.postID and v2.username='username'
GROUP BY 1,2,3,4,v2.vote
ORDER BY 5 DESC

DEMO