How do you find 2×Vinyl via the database

MusicBrainz Development
1

I’m using the data dumps in Postgres to query for all vinyl associated with an artist. The medium format table contains a number of formats for vinyl but no mention of 2×Vinyl that is referred in the website when you’re browsing.

I have queried a number of albums where the medium is 2xVinyl and the query doesn’t even reference it as vinyl at all. With Albums that a just have one vinyl disc are referenced correctly however.

What am I missing here? Thanks!

2

Releases with 2xVinyl would have two separate mediums, each with format Vinyl. So you are looking for mediums that are associated with the same release.

3 Likes
3

Thanks for your reply.

That makes sense but when I run a query on releases that I know for sure has 2xVinyl I don’t receive anything related to vinyl. Screenshot for reference.

Screen Shot 2023-01-31 at 4.11.43 PM
Screen Shot 2023-01-31 at 4.11.43 PM1198×626 95 KB

Clearly I am missing something here!

4

What’s the actual query?

1 Like
5

This is query I am using to test.

SELECT DISTINCT ON (release.id)
	id,
	name,
	medium.medium_id,
	medium.format,
	medium.position,
	release.barcode,
	release.release_group
FROM
	release
	INNER JOIN (
		SELECT
			id AS medium_id,
			medium.track_count,
			medium.position,
			format
		FROM
			medium) medium ON medium.medium_id = release.id
WHERE
	release.name = 'A Moon Shaped Pool'```
1 Like
6

Here is the trouble :slight_smile: You are joining releases and mediums which accidentally have the same ID.

You want to select mediums with a matching release ID, not medium ID. Not fully sure about the field name right now, but probably release_id. Check the medium table.

3 Likes
7

This is the field release in the table medium.

See entity network overview

3 Likes
8

Looks like it’s just release:

CREATE TABLE medium ( -- replicate (verbose)
    id                  SERIAL,
    release             INTEGER NOT NULL, -- references release.id
    position            INTEGER NOT NULL,
    format              INTEGER, -- references medium_format.id
    name                VARCHAR NOT NULL DEFAULT '',
    edits_pending       INTEGER NOT NULL DEFAULT 0 CHECK (edits_pending >= 0),
    last_updated        TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    track_count         INTEGER NOT NULL DEFAULT 0
);
1 Like
9

Thanks everyone! You’re right it was a bad join. It’s always something small like that!

1 Like