PostgreSQL - SELECT with two many2many relations

Question

Context : I have a table POST related to TAGS and CATEGORIES (all many2many relations). I want to return posts even if they don't have any TAGS or CATEGORIES, both in respective arrays.

Example :

 - POST1 with TAG1, TAG2 and CAT1  
 - POST2 WITH CAT3, CAT4, CAT5
 - POST3

I want to return :

 - POST1 / {TAG1, TAG2} / {CAT1}
 - POST2 / NULL / {CAT3, CAT4, CAT5}
 - POST3 / NULL / NULL

But I have :

 - POST1 / {TAG1, TAG2} / {CAT1, NULL, NULL}
 - POST2 / {NULL, NULL} / {CAT3, CAT4, CAT5}
 - POST3 / {NULL, NULL} / {NULL, NULL, NULL}

If screenshot doesn't work, part of my code for relation :

`FROM bmb_blog_post as post 
LEFT OUTER JOIN bmb_blog_category_bmb_blog_post_rel as posts_categories ON posts_categories.post_id = post.id
LEFT OUTER JOIN bmb_blog_category as category ON category.id = posts_categories.category_id
LEFT OUTER JOIN bmb_blog_post_bmb_blog_tag_rel as posts_tags ON posts_tags.post_id = post.id 
LEFT OUTER JOIN bmb_blog_tag as tag ON tag.id = posts_tags.tag_id`

PgAdmin SQL Results Screenshot

Answer 1

If I understand correctly, then way is with two subqueries where you aggregate before doing the final joins:

SELECT p.id, pc.categories, pt.tags
FROM bmb_blog_post p LEFT OUTER JOIN
     (SELECT pc.post_id, array_agg(c.category) as categories
      FROM bmb_blog_category_bmb_blog_post_rel pc JOIN
           bmb_blog_category c
           ON c.id = pc.category_id
      GROUP BY pc.post_id
     ) pc
     ON pc.post_id = p.id LEFT OUTER JOIN 
     (SELECT pt.post_id, array_agg(t.tag) as tags
      FROM bmb_blog_post_bmb_blog_tag_rel pt JOIN
           bmb_blog_tag t
           ON t.id = pt.tag_id
      GROUP BY pt.post_id
     ) pt
     ON pt.post_id = p.id ;

Your results would be even more outlandish if you had posts that had multiple categories and multiple tags.