hey guys, this is my sql query to get the tracks and their associated releases/release groups for all iranian singers
2023-11-01 30544, 2023
jazzi
SELECT a.name, a.comment, t.name, rg.id
2023-11-01 30544, 2023
jazzi
FROM artist a join artist_credit_name acn on a.id=acn.artist
2023-11-01 30545, 2023
jazzi
join track t on t.artist_credit=acn.artist_credit
2023-11-01 30545, 2023
jazzi
join medium m on t.medium=m.id
2023-11-01 30546, 2023
jazzi
join release r on m.release=r.id
2023-11-01 30546, 2023
jazzi
join release_group rg on r.release_group=rg.id
2023-11-01 30547, 2023
jazzi
WHERE UPPER(a.comment) LIKE '%IRAN%' OR area=101 or Upper(a.comment) LIKE '%PERSIA%' ;
2023-11-01 30547, 2023
jazzi
how could i extend this to get the composer/lyricist information?
2023-11-01 30532, 2023
bitmap
that will require a lot of joins, so not sure it makes sense to do all in one query :)
2023-11-01 30542, 2023
bitmap
they are stored in l_artist_work but to get the work you have to go through l_recording_work
2023-11-01 30545, 2023
jazzi
bitmap ahh ok so like this? SELECT DIstinct a.name, a.comment, t.name,rec.id, artwork.*
2023-11-01 30546, 2023
jazzi
FROM artist a join artist_credit_name acn on a.id=acn.artist
2023-11-01 30546, 2023
jazzi
join track t on t.artist_credit=acn.artist_credit
2023-11-01 30547, 2023
jazzi
join recording rec on t.recording=rec.id
2023-11-01 30547, 2023
jazzi
join l_recording_work recwork on rec.id=recwork.entity0
2023-11-01 30548, 2023
jazzi
join l_artist_work artwork on recwork.entity1=artwork.entity1
2023-11-01 30548, 2023
jazzi
WHERE UPPER(a.comment) LIKE '%IRAN%' OR area=101 or Upper(a.comment) LIKE '%PERSIA%' ;
2023-11-01 30528, 2023
ttpcodes has quit
2023-11-01 30538, 2023
ttpcodes_ joined the channel
2023-11-01 30526, 2023
bitmap
jazzi: I think so. you can probably skip the recording join, and just do t.recording=recwork.entity0. also, to filter the type of l_artist_work relationship, join link on link.id = artwork.link, and check that link.link_type in (165, 167, 168).
2023-11-01 30546, 2023
bitmap likes the alias "artwork" for l_artist_work
2023-11-01 30531, 2023
bitmap
if you want the actual artist names / mbids, join the artist table a second time to l_artist_work.entity0
FROM artist a join artist_credit_name acn on a.id=acn.artist
2023-11-01 30500, 2023
jazzi
join track t on t.artist_credit=acn.artist_credit
2023-11-01 30500, 2023
jazzi
join recording rec on t.recording=rec.id
2023-11-01 30501, 2023
jazzi
join l_recording_work recwork on rec.id=recwork.entity0
2023-11-01 30501, 2023
jazzi
join l_artist_work artwork on recwork.entity1=artwork.entity1
2023-11-01 30502, 2023
jazzi
join artist b on b.id=artwork.entity0
2023-11-01 30502, 2023
jazzi
WHERE
2023-11-01 30503, 2023
jazzi
a.comment ~* '\y(IRAN|IRANIAN|PERSIA|PERSIAN)\y'
2023-11-01 30503, 2023
jazzi
OR a.area=101
2023-11-01 30504, 2023
jazzi
i tried doing artist (b) .* but i dont see how to get the link type ID's
2023-11-01 30543, 2023
bitmap
join link l on (l.id=l_artist_work.link AND l.link_type IN (165, 167, 168))
2023-11-01 30545, 2023
anonn joined the channel
2023-11-01 30534, 2023
bitmap
btw, for pasting long sql queries in irc, use pastebin.com to avoid spamming the channel :)
2023-11-01 30545, 2023
jazzi
bitmap thank you so much it works now i think
2023-11-01 30554, 2023
jazzi
oh im sorry ill do that next time
2023-11-01 30513, 2023
bitmap
np
2023-11-01 30530, 2023
jazzi
just one last question, sorry for all the questions... is my method of doing the Where clause above with the ~ appropriate to get all the iranian/persian singers songs?
2023-11-01 30547, 2023
jazzi
and also including the area code clause of iran
2023-11-01 30555, 2023
bitmap
one improvement you could do is to use area_containment to check if a.area is a sub-area of iran
2023-11-01 30551, 2023
bitmap
but if you are using a replicated mirror server, you may have to build this table...
2023-11-01 30548, 2023
bitmap
join area_containment ac on ac.descendant = a.area, and then WHERE a.area = 101 OR ac.parent = 101 (I think)
2023-11-01 30534, 2023
bitmap
are you using musicbrainz-docker?
2023-11-01 30535, 2023
jazzi
bitmap i ahh i assume this is the line sudo docker-compose exec musicbrainz bash -c './admin/BuildMaterializedTables --database=MAINTENANCE all' to do this for the local postgres installation?
oh and is this appropriate for getting all the genres for the tracks? u gave me this advice yesterday on how to do it but i never checked if this was fully correct
2023-11-01 30518, 2023
bitmap
looks reasonable
2023-11-01 30552, 2023
bitmap
but instead of DISTINCT it might make the results easier to work with if you use GROUP BY and then array_agg(genre.name) ?
2023-11-01 30508, 2023
bitmap
so there is not a duplicate row for each genre
2023-11-01 30539, 2023
jazzi
oh i didnt know u could do that lol yeah thatd be better ty
oh also i just assumed the release_group_type was 1 for album, 2 for single and 3 for ep but maybe theres documentation on this? like im not sure what 11 means
2023-11-01 30540, 2023
bitmap
yeah that looks better
2023-11-01 30528, 2023
bitmap
you can strip NULL from the genres array with FILTER - array_agg(DISTINCT genre.name) FILTER (WHERE genre.name IS NOT NULL) AS genre_names
2023-11-01 30525, 2023
bitmap
hmm, I'm not sure if we document the RG type IDs on the website, but you can just do `SELECT id, name FROM release_group_primary_type` to list them all
2023-11-01 30556, 2023
bitmap
11 appears to be "Other"
2023-11-01 30522, 2023
jazzi
right got u, i just had a scary thought if my queries were taking into consideration 'featured artists' or is that included in the links
2023-11-01 30510, 2023
bitmap
those should be included in the track artist credit, if they were entered correctly
2023-11-01 30554, 2023
jazzi
ah so track.artist credit will give me the artist ids who featured
2023-11-01 30504, 2023
jazzi
then i just need to join that back to the artist to get there names
2023-11-01 30513, 2023
jazzi
i might do this in a seperate query lol
2023-11-01 30539, 2023
iconoclasthero has quit
2023-11-01 30553, 2023
relaxoMob has quit
2023-11-01 30506, 2023
relaxoMob joined the channel
2023-11-01 30510, 2023
jazzi
bitmap that command to build the area containment didnt do anything. perhaps i need to change the database in the line to 'musicbrainz_db'? like this: sudo docker-compose exec musicbrainz bash -c './admin/BuildMaterializedTables --database=musicbrainz_db area_containment'
2023-11-01 30538, 2023
jazzi
im just using the local postgres version of the database not the full server btw
2023-11-01 30505, 2023
bitmap
did it log any output? if there is anything in the table in will be skipped, so maybe it was built already
2023-11-01 30549, 2023
bitmap
the name passed to --database corresponds to an entry in DBDefs.pm (in the register_databases call)
2023-11-01 30500, 2023
bitmap
so either MAINTENANCE or READWRITE should work
2023-11-01 30520, 2023
jazzi
it didnt log any output no, but i also tried select * from area_containment and it returns empty
2023-11-01 30556, 2023
bitmap
weird, try with READWRITE. you can also pass --force to the script so it builds even if it's non-empty
2023-11-01 30524, 2023
jazzi
hmm ive tried both those changes still didn't work
2023-11-01 30537, 2023
jazzi
i assume its not a major problem cause most iranian artists have the word iran or iranian in their comments?
2023-11-01 30546, 2023
jazzi
atleast i hope haha
2023-11-01 30557, 2023
jazzi
or are just area 101
2023-11-01 30530, 2023
jazzi
oh maybe theres a way to get all the subareas and i could just add to the sql query manually one by one?
2023-11-01 30551, 2023
bitmap
hmm, the command should log something though, are you running it from the musicbrainz-docker dir?
2023-11-01 30506, 2023
jazzi
yeah i cd into cd musicbrainz-docker
2023-11-01 30517, 2023
jazzi
and i do sudo docker ps by container shows up correctly
2023-11-01 30544, 2023
jazzi
and i ran from there and it just outputs nothing goes back to my username
