SELECT DISTINCT FROM releases AS JOIN SELECT DISTINCT release_id array

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
SELECT DISTINCT *
FROM releases AS r
JOIN (
SELECT DISTINCT release_id, array_agg(name) as authors
FROM authors as a
JOIN releases_authors AS ra
ON ra.author_id = a.id
GROUP BY release_id
) AS a
ON r.id = a.release_id
JOIN (
SELECT DISTINCT release_id, array_agg(name) AS tags
FROM tags
JOIN releases_tags AS rt
ON rt.tag_id = tags.id
GROUP BY release_id
) AS t
ON r.id = t.release_id
ORDER BY date DESC NULLS LAST limit 4