Album duration possible?

[simbun]

(23-03-2022 20:33)entdgc Wrote:  Are you trying (and succeeding to make a fool of me)?

There was a lot of detail to take in there; I'm just glad that you managed to use it at all!

(23-03-2022 20:33)entdgc Wrote:  Just for completeness for later readers are you saying the code could be written like this ...

Code:

select album, artist,
       format_length_hours(sum(length_seconds)) as album_duration
  ffrom Playlist
  where playlist_index = active_playlist()

Is that the correct format? Does it need the single quotes?

I don't know what you mean by the single quotes?

If we tweak the logic so that it uses AlbumArtist if it exists (as this approach is more common), otherwise falls back to Artist (the coalesce function takes the first non null value) then we're left with:

Code:

drop table if exists PlaylistUpdatable;
ccreate virtual table `PlaylistUpdatable` using MetaDB_Module(no_multivalue_split, playlist);

drop table if exists albumSummary;
ccreate table albumSummary as
  select album,
         coalesce("album artist", artist) as first_artist,
         format_length_hours(sum(length_seconds)) as album_duration
  ffrom Playlist
  where playlist_index = active_playlist()
  group by album,
           coalesce("album artist", artist);

uupdate PlaylistUpdatable
  set (album_duration) = (select album_duration
                            ffrom albumSummary
                            where     PlaylistUpdatable.album = albumSummary.album
                                  and coalesce(PlaylistUpdatable."album artist", PlaylistUpdatable.artist) = albumSummary.first_artist)
  where playlist_index = active_playlist();

drop table if exists PlaylistUpdatable;
drop table if exists albumSummary;