I'm trying to get all likes/dislikes of multiple post. The problem can be summarized as:
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
likes table and the
array_agg function but I can't figure out how to get two different arrays for likes and dislikes.