Two array_agg at once

Question

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.

Answer 1

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;