Search code examples
postgresqlmusicbrainz

Musicbrainz SQL query to get tracks for release_group

I am using the musicbrainz.org postgresql database, which I have installed locally and have accessed via python.

The database is a list of music artists and associated criteria. Here is the schema : enter image description here

How can I create a SQL query that outputs all tracks for a release group? I am able to get the proper artist info and releases associated with a specific band but the tracks for the release are wrong using the below query's:

strsql_band = "SELECT artist.id, artist.gid, artist.name, artist.comment FROM artist WHERE artist.name=%s AND type>1 ORDER BY type, artist.last_updated DESC"
strsql_memberid = "SELECT entity0, link.begin_date_year, link.end_date_year FROM l_artist_artist l JOIN link ON l.link=link.id WHERE entity1=%s AND link_type=103"
strsql_release = "SELECT id, release_group.name FROM release_group WHERE artist_credit=%s"
# This does not return the correct tracks for the release
strsql_track = "SELECT id, position, name, length FROM track WHERE artist_credit=%s LIMIT 15"

Any help would be greatly appreciated, I have been banging my head on this for hours now.

Solution

  • medium and track table will give you this information

    Here is the query that worked for my case:

    SELECT
    array_agg(track.name) as track_list,
    release.gid as release_gid,
    release.release_group as release_group_id
FROM track
INNER JOIN medium
 ON track.medium=medium.id
INNER JOIN release
 ON medium.release=release.id
GROUP BY release.gid, release.release_group limit 5;

    Sample Results

    release_gid release_group   track_list
00000363-74c9-4c3b-bef7-a433eb9687e3    950559  {"Small Screen",Alice,Dauntless,Lullaby,"Grinding the Mill","Four Colors","2000 Gods","This Life","More Than Meets the Eye","The World"}
00000ba0-d5e2-41d7-9c5d-48e8eb04226a    388941  {"Ai, Vanna","Dod māmiņa, kam dodama","Mēs sūdzēsimies Strazdbūrā","Ar Eiropu plecos","Mīļais mans (Oi ļuļi, oi ļuļi)","Tu tik lūr, monamūr","Kocēni, Dikļi un Tožas","Sev augstu laimi vēlēju","Happy mērsrags","Tur es dzēru, tur man tika","Pritonā pie eglītes","Kur ir manas dāvaniņas","Iespraud svecīti","Ints un Ģirts","Mazs putniņš kājām... (Vai Rīga gatava?)","Melngalvju blūzs",Siergriezējs,"Zeva dziesma","Kentaura skaistumkopšanas salonā","Es sakūru uguntiņu (Gaisā skrēja)","Tu nesit... (Nestandarta žoklis)"}
00000ff3-8918-400f-89d5-ec80f52465bf    530779  {"SING IT","Precious Days",トキメキ}
0000172d-6e43-4d7d-8647-da718593a97a    2382060 {"J'ai besoin d'un doliprane","Cœur brisé","Mon proprio","J'me demande","Extra extra terrestre","Bienvenue chez moi (dans ma coloc)","L'amour c'est du pipeau","La caissière du casino",Jean-Marc,"Toutes des salopes"}