mherger wrote:
> > This goes right back to my long-standing feeling that album artist(s)
> > roles need to be assigned for _every_ album at scan time. Without
> them,
> > the queries and the logic involved just to generate an album's "by"
> line
> > are very inefficient. A page requiring the display of dozens of
> albums
> > can require hundreds of queries instead of the single query that
> should
> > be necessary.
>
> Well, it wouldn't solve this problem here: as we can have multiple album
>
> artists, we'd still have to look them up in a separate table, adding the
>
> same overhead.
If every album had one or more contributors with albumartist roles,
generating an album list should be doable in a single query by using the
group_concat() function. The following query would generate a full list
of albums sorted by artist/year/title, with all album artists for each.
Code:
--------------------
SELECT ca.album AS album_id,
CAST(a.title AS CHAR) AS album,
a.year,
GROUP_CONCAT(ca.contributor, ", ") AS album_artist_id,
GROUP_CONCAT(CAST(c.name AS CHAR), ", ") AS album_artist,
GROUP_CONCAT(c.namesort, " ") AS album_artist_sort
FROM contributor_album ca
INNER JOIN albums a ON a.id = ca.album
INNER JOIN contributors c ON c.id = ca.contributor
WHERE ca.role = 5
GROUP BY ca.album
ORDER BY album_artist_sort, a.year, a.titlesort;
--------------------
Even if you had to do a separate query for every album, it would be
faster than the old method. IIRC, it went something like the following
for every album in a list. Since the typical user will have no explicit
ALBUMARTIST tag in common single-artist albums, it means that it
required three queries for most albums.
Query contributor_albums joined with contributors for the role of
albumartist
If records > 0 we're done
Query contributor_albums joined with contributors for the role of band
If records > 0 we're done
Query contributor_albums joined with contributors for the role of
artist
> FWIW: I think I have some working code to get the full list of
> contributors. It does indeed come with a performance penalty. Whether
> this penalty hurts or not, I can't say. I'd like to make it optional
> anyway, as after a scan we should know whether the user has multiple
> contributors for those roles or not. And some users probably don't want
>
> to have potentially long lists of contributors eg. in the web UI's album
>
> list, preferring the slightly incomplete list we have today.
With compilations, you should never display all of the track artists.
Non-compilations with many track artists are poorly tagged if they don't
have an album artist. I suppose you could always cut off the display at
some maximum X number of characters.
> >> Do you have a list of places where this incomplete list of
> contributors
> >> would be shown?
> >
> > I'm not aware of any place where it is -not- shown for albums. Have
> you
> > found one?
>
> When you drill down into an album (to the track list), you would get the
>
> full list, wouldn't you? I think this limitation is mostly about album
> lists.
The track list page appears to simply pull all contributors (not sure if
it uses one or multiple queries, but it doesn't really matter) and then
it displays them by role - album artists, artists, track artists, etc.
So, yes, album lists.
------------------------------------------------------------------------
JJZolx's Profile: http://forums.slimdevices.com/member.php?userid=10
View this thread: http://forums.slimdevices.com/showthread.php?t=101895
_______________________________________________
beta mailing list
beta@lists.slimdevices.com
http://lists.slimdevices.com/mailman/listinfo/beta
