|
If you were logged in you would be able to see more operations.
|
|
|
Current query pick up many obvious groups. I think we should use query that exclude all groups that have members from results. Here is modified query
WITH bands AS (
SELECT DISTINCT artist.id, artist.gid, artist.type, artist.name
FROM
artist
JOIN l_artist_artist ON l_artist_artist.entity1=artist.id
JOIN link ON link.id=l_artist_artist.link
JOIN link_type ON link_type.id=link.link_type
WHERE
artist.type = 2 AND
link_type.name = 'member of band'
), artist AS (
SELECT DISTINCT artist.id, artist.gid, artist.type, artist.name
FROM
artist
JOIN l_artist_artist ON l_artist_artist.entity0=artist.id
JOIN link ON link.id=l_artist_artist.link
JOIN link_type ON link_type.id=link.link_type
WHERE
(artist.type = 2 OR artist.type IS NULL) AND
link_type.name NOT IN ('collaboration')
EXCEPT
SELECT * FROM bands
)
SELECT DISTINCT ON(name.name, artist.id) artist.gid, name.name, artist.type
FROM
artist
JOIN artist_name AS name ON artist.name=name.id
ORDER BY name.name, artist.id
|
|
Description
|
Current query pick up many obvious groups. I think we should use query that exclude all groups that have members from results. Here is modified query
WITH bands AS (
SELECT DISTINCT artist.id, artist.gid, artist.type, artist.name
FROM
artist
JOIN l_artist_artist ON l_artist_artist.entity1=artist.id
JOIN link ON link.id=l_artist_artist.link
JOIN link_type ON link_type.id=link.link_type
WHERE
artist.type = 2 AND
link_type.name = 'member of band'
), artist AS (
SELECT DISTINCT artist.id, artist.gid, artist.type, artist.name
FROM
artist
JOIN l_artist_artist ON l_artist_artist.entity0=artist.id
JOIN link ON link.id=l_artist_artist.link
JOIN link_type ON link_type.id=link.link_type
WHERE
(artist.type = 2 OR artist.type IS NULL) AND
link_type.name NOT IN ('collaboration')
EXCEPT
SELECT * FROM bands
)
SELECT DISTINCT ON(name.name, artist.id) artist.gid, name.name, artist.type
FROM
artist
JOIN artist_name AS name ON artist.name=name.id
ORDER BY name.name, artist.id |
Show » |
Sort Order:
|
Improvement
Closed
Normal
Report: Artists that may be persons shoudn't show obvious groups
The existing query looks flawed in more ways than just this – it's grabbing every artist<->artist link that's not a collaboration and using entity0 from that.
Seems like what it should be doing, in broad strokes, is:
a.) getting everything that's on the relevant side of an artist-artist link that could denote personhood
b.) getting everything that's on the relevant side of an artist-artist link that probably denotes a group
c.) return results of a. minus results of b. filtered to things that aren't currently set to person
Possibly a. and b. should be extended to all link types if we can find anything in the others that implies personhood, but I don't think there are any.
Link types that seem applicable to a. include: collaborator on (entity0), voice actor (entity0), member of (entity0), conductor (entity0), is person/legal name (both ways), catalogued by (entity1), and both sides of everything in 'personal relationship' (parent, sibling, married, 'involved with' (do we even really use that last one? apparently there's 197 of them, crazy))
Link types for b. include member of (entity1), collaborator on (entity1), conductor (entity1).
How does that sound (to anyone who might happen by)?
(edit: removed supporting musician from a., added conductor to b.)