-
Type:
Improvement
-
Resolution: Fixed
-
Priority:
Normal
-
Affects Version/s: None
-
Component/s: Reports
-
None
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