一次兩個array_agg

我正在嘗試獲取多個文章的所有喜歡/不喜歡的內容。問題可以概括為：

• posts表：id, content, user_id(fk)
• users表：id，name
• likes表：post_id（fk），user_id（fk）is_liked，，is_disliked

為了適合我的案例，我需要獲取所有喜歡/不喜歡的列表，所以我首先嘗試了這個：

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

這顯然沒有優化。這就是為什麼我join在likes桌子上和array_agg功能上嘗試了另一個東西，但我不知道如何獲得兩個不同的數組來表示喜歡和不喜歡。

這應該很簡單，array_agg還有附加FILTER條款：

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;

引用自：https://dba.stackexchange.com/questions/261085