Artist recordings ordered by release date query

MusicBrainz Development
1

hi there, I am working on a query to obtain all the recordings from a given artist ordered by release data.

after examining the musicbrainz’s database scheme, I ended up with this query:

WITH oldest_release_group AS (
    SELECT rec.id AS recording_id, 
           MIN(rgm.first_release_date_year) AS first_release_date_year,
           MIN(rgm.first_release_date_month) AS first_release_date_month,
           MIN(rgm.first_release_date_day) AS first_release_date_day
    FROM recording rec
    JOIN track t ON t.recording = rec.id
    JOIN medium m ON m.id = t.medium
    JOIN release r ON r.id = m.release
    JOIN release_group rg ON rg.id = r.release_group
    JOIN release_group_meta rgm ON rgm.id = rg.id
    JOIN artist_credit_name acn ON acn.artist_credit = rec.artist_credit
    WHERE acn.artist = 1
    GROUP BY rec.id
)
SELECT rec.id AS recording_id, rec.name AS recording_name, 
       rg.id AS release_group_id, rg.gid AS release_group_gid, rg.name AS release_group_name, 
       rg.type AS primary_type_id, rg.artist_credit AS artist_credit_id, rg.edits_pending, 
       rgm.first_release_date_year, rgm.first_release_date_month, rgm.first_release_date_day
FROM recording rec
JOIN track t ON t.recording = rec.id
JOIN medium m ON m.id = t.medium
JOIN release r ON r.id = m.release
JOIN release_group rg ON rg.id = r.release_group
JOIN release_group_meta rgm ON rgm.id = rg.id
JOIN artist_credit_name acn ON acn.artist_credit = rec.artist_credit
JOIN oldest_release_group org ON org.recording_id = rec.id 
                              AND org.first_release_date_year = rgm.first_release_date_year
                              AND org.first_release_date_month = rgm.first_release_date_month
                              AND org.first_release_date_day = rgm.first_release_date_day
WHERE acn.artist = 1
ORDER BY rgm.first_release_date_year, rgm.first_release_date_month, rgm.first_release_date_day;

is this the correct approach? thanks in advance

2

It looks like you’re taking the minimum of all the years, the minimum of all the months, and the minimum of all the days. That’s probably not what you want (think about what happens if you have two RGs, one with a first release date of 2010-05-12 and another with a first release date of 2009-12-31).

I’m not a PostgreSQL expert, but something like

MIN(format('%04d-%02d-%02d', rgm.first_release_date_year, rgm.first_release_date_month, rgm.first_release_date_day)) as first_release_date

may be closer to what you want there.

If that works, you’ll also need to update the join condition in the main SELECT query. You’ll probably want to GROUP BY rec.id again since multiple RGs can (and often do) have the same release date.

2 Likes