1

I'm trying to get all likes/dislikes of multiple post. The problem can be summarized as:

  • posts table: id, content, user_id (fk)
  • users table: id, name
  • likes table: post_id (fk), user_id (fk), is_liked, is_disliked

To fit my use case, I need to get the list of all likes/dislikes so I've first tried this:

SELECT 
    posts.id, posts.content, users.name as author
    ARRAY(SELECT user_id FROM likes WHERE is_liked and post_id = posts.id) as likes,
    ARRAY(SELECT user_id FROM likes WHERE is_disliked and post_id = posts.id) as dislikes
FROM 
   posts
   INNER JOIN users ON posts.user_id = users.id

This is clearly not optimized. That's why I've tried something with another join on likes table and the array_agg function but I can't figure out how to get two different arrays for likes and dislikes.

2

That should be simple with array_agg and additional FILTER clauses:

SELECT 
    posts.id, posts.content, users.name as author
    array_agg(likes.user_id) FILTER (WHERE likes.is_liked) as likes,
    array_agg(likes.user_id) FILTER (WHERE likes.is_disliked) as dislikes
FROM posts
   INNER JOIN users ON posts.user_id = users.id
   INNER JOIN likes ON likes.post_id = posts.id
GROUP BY posts.id, posts.content, users.name;
2
  • Available since Postgres 9.4, not enough people know about the filter clause. – Colin 't Hart Mar 3 '20 at 13:52
  • Indeed, I wasn't aware of it, thank you very much! – RobinFrcd Mar 3 '20 at 14:05

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.