CODEWITHSUNDEEP
postgresql
Alec Bennett
Alec Bennett

Reputation: 5887

Postgresql Multi Table Count: likes, dislikes, comments for social media style app

Trying to build a query in postgresql to pull comments, likes, and dislikes out for a 'post'. Currently, the query is miscounting, and seems to give extra values when both comments/likes occur for the same post.

SQLFiddle is available here: http://sqlfiddle.com/#!17/e0f51/1

Currently, the results look like this:

postid  postbody      comment_count dislikes    likes
1       Test Post A   1             0           0 
2       Test Post B   2             2           4
3       Test Post C   0             0           0
4       Test Post D   2             4           2
5       Test Post E   0             1           3

But should ideally look like this:

postid  postbody      comment_count dislikes    likes
1       Test Post A   1             0           0 
2       Test Post B   2             1           2
3       Test Post C   0             0           0
4       Test Post D   2             2           1
5       Test Post E   0             1           3

Upvotes: 3

Views: 975

Answers (1)

carrot
carrot

Reputation: 96

The issue will occur when there is more than 1 comment, the like/dislike will be multiplied by the amount of comments received.

Best way round this, is to nest the like/dislike into sub queries rather the joins... 

SELECT posts.postid, posts.postbody,
  COUNT(DISTINCT comments.commentid) AS comment_count, 
  (SELECT COUNT(nullif(postlikes.vote,true)) FROM postlikes WHERE postlikes.postid=posts.postid)  AS dislikes, 
  (SELECT COUNT(nullif(postlikes.vote,false))FROM postlikes WHERE postlikes.postid=posts.postid)  AS likes 
  FROM posts 
  LEFT JOIN comments ON comments.postid=posts.postid 
  GROUP BY posts.postid;

Upvotes: 4

Related Questions