Changeset 4abe330 for music

Timestamp:

05/01/25 12:32:20 (2 weeks ago)

Author:

ManuelTrajcev <manueltrajcev7@…>

Branches:

master

Children:

59b2e9c

Parents:

611686e

Message:

rank_list_artists view update + bootstrap styling

Location:

music

Files:

• __pycache__/views.cpython-312.pyc (modified) ( previous)
• views.py (modified) (1 diff)
• views/rank_list_artists.sql (modified) (1 diff)

music/views.py

@@ -79 +79 @@
         rows = cursor.fetchall()
 
-    data = [{'name': row[0], 'num_invoices': row[1]} for row in rows]
+    data = [{'name': row[0], 'num_invoices': row[1], 'money_earned': row[2]} for row in rows]
 
     return render(request, 'rank_list_artists.html', {'data': data})

music/views/rank_list_artists.sql

@@ -1 +1 @@
 CREATE VIEW rank_list_artists AS
-    SELECT
-            (ar.name),
-            count(il.invoice_line_id)
-as num_invoices
-    FROM artist ar
-    left join album al on ar.artist_id = al.album_id
-    left join track tr on al.album_id = tr.album_id
-    left join invoice_line il on tr.track_id = il.track_id
-    group by ar.name
-    order by num_invoices desc
+SELECT ar.name,
+       count(il.invoice_line_id) AS num_invoices,
+       COALESCE(SUM(i.total), 0) AS money_earned
+FROM artist ar
+         LEFT JOIN album al ON ar.artist_id = al.album_id
+         LEFT JOIN track tr ON al.album_id = tr.album_id
+         LEFT JOIN invoice_line il ON tr.track_id = il.track_id
+         LEFT JOIN invoice i on il.invoice_id = i.invoice_id
+GROUP BY ar.name
+ORDER BY money_earned DESC;