Changes between Version 9 and Version 10 of AdvancedDatabaseDevelopment

Timestamp:

08/05/26 11:59:23 (21 hours ago)

Author:

231136

Comment:

--

AdvancedDatabaseDevelopment

@@ -399 +399 @@
     me.genre AS song_genre,
     s.link AS song_link,
-    alb_me.id AS album_id,
-    alb_me.title AS album_title,
+    coalesce(alb_me.id, -1) AS album_id,
+    coalesce(alb_me.title, '/') AS album_title,
     u.user_id AS artist_user_id,
     u.full_name AS artist_name,
-    u.username AS artist_username
-FROM project.songs s
-         JOIN project.musical_entities me ON s.id = me.id
-         JOIN project.albums alb ON s.album_id = alb.id
-         JOIN project.musical_entities alb_me ON alb.id = alb_me.id
-         JOIN project.artists art ON me.released_by = art.user_id
-         JOIN project.non_admin_users nau ON art.user_id = nau.user_id
-         JOIN users u ON nau.user_id = u.user_id;
+    u.username AS artist_username,
+    contrib.contributors
+FROM songs s
+         JOIN musical_entities me ON s.id = me.id
+         left JOIN albums alb ON s.album_id = alb.id
+         left JOIN musical_entities alb_me ON alb.id = alb_me.id
+         JOIN artists art ON me.released_by = art.user_id
+         JOIN non_admin_users nau ON art.user_id = nau.user_id
+         JOIN users u ON nau.user_id = u.user_id
+         LEFT JOIN LATERAL (
+             SELECT STRING_AGG(cu.full_name, ', '
+                               ORDER BY c.contribution_order) AS contributors
+             FROM contributions c
+             JOIN users cu ON cu.user_id = c.artist_id
+             WHERE c.musical_entity_id = s.id
+         ) contrib ON TRUE;
 }}}