When calculating the average playcount for every album, how do I ignore tracks with playcount 0?

avg(nullif(CAST(playcount AS INTEGER),0))

Quote from: chrisdukes on 2024-03-30 00:29:27

Can you only refresh a tree if there's a query?

This question is not clear. Could you please elaborate? Do you mean with tree actually a tree node? Do you mean with query the query tab?

Just wondering if there are some possible optimizations I can do in the queries or the settings.

SELECT
    SUBSTR(name, 1, 1) AS first_letter,
    name
FROM
    pragma_table_info('MediaLibrary')
WHERE
name != 'bitrate' AND
name != 'channels' AND
name != 'codec' AND
name != 'filesize' AND
name != 'filesize_natural' AND
name != 'length' AND
name != 'length_ex' AND
name != 'length_samples' AND
name != 'length_seconds' AND
name != 'length_seconds_fp' AND
name != 'samplerate' AND
name != 'album artist' AND
name != 'album' AND
name != 'artist' AND
name != 'comment' AND
name != 'composer' AND
name != 'date' AND
name != 'discnumber' AND
name != 'genre' AND
name != 'performer' AND
name != 'original album' AND
name != 'totaldiscs' AND
name != 'totaltracks' AND
name != 'tracknumber' AND
name != 'title'
ORDER BY
    first_letter, name

So I was trying to use DATEDIFF(day, CURRENT_TIMESTAMP, max_lastplayed) to get the number of days since last played, but the component doesn't seem to know what to do with the interval parameter ("day") and shows an error.

So, to do that, I would like to make query that lists every field name and populates a playlist with all of the tracks that use that field.

Quote from: dpc666 on 2024-04-03 15:00:48

So I was trying to use DATEDIFF(day, CURRENT_TIMESTAMP, max_lastplayed) to get the number of days since last played, but the component doesn't seem to know what to do with the interval parameter ("day") and shows an error.

Of course. DATEDIFF is not a known function in SQLite. See here for the supported date functions in SQLite.

When it comes to functions in SQL there is no real standard. So you cannot go to just an arbitrary SQL web page and use what you find there. The best is that you stick with the SQLite documentation and the custom sql functions documented in the SQLite utilities help file.

DROP TABLE IF EXISTS BadFieldTable;
CREATE TABLE BadFieldTable AS

SELECT CASE WHEN [composers] IS NOT NULL THEN 'composers' END AS Node1, album, discnumber, tracknumber, path, subsong FROM MediaLibrary WHERE [composers] IS NOT NULL
UNION ALL SELECT CASE WHEN [composersort] IS NOT NULL THEN 'composersort' END AS Node1, album, discnumber, tracknumber, path, subsong FROM MediaLibrary WHERE [composersort] IS NOT NULL
UNION ALL SELECT CASE WHEN [composersortorder] IS NOT NULL THEN 'composersortorder' END AS Node1, album, discnumber, tracknumber, path, subsong FROM MediaLibrary WHERE [composersortorder] IS NOT NULL
UNION ALL SELECT CASE WHEN [conductor] IS NOT NULL THEN 'conductor' END AS Node1, album, discnumber, tracknumber, path, subsong FROM MediaLibrary WHERE [conductor] IS NOT NULL
;

SELECT
Node1 || ' (' || COUNT(path) || ')' AS NodeCount
FROM BadFieldTable
GROUP BY Node1
ORDER BY COUNT(path) DESC, album, discnumber, tracknumber COLLATE NaturalNoCase

SELECT
Node1
FROM BadFieldTable
GROUP BY Node1
ORDER BY Node1, album, discnumber, tracknumber COLLATE NaturalNoCase

This Query sends tracks to the playlist as expected

so I guess it has something to the do with the COUNT(path) that I added.

I found this post in the forum and the part where fbuser says "You need to define queries in that way, that the result is one track per row" maybe my problem.  But, modifying the suggestions to my problem didn't work.  

SELECT
Node1 || ' (' || count(Node1) OVER(PARTITION BY Node1) || ')'
FROM BadFieldTable
ORDER BY Node1, album, discnumber, tracknumber COLLATE NaturalNoCase

DROP TABLE IF EXISTS BadFieldTable;
CREATE TABLE BadFieldTable AS

SELECT CASE WHEN [composers] IS NOT NULL THEN 'composers' END AS Node1, album, discnumber, tracknumber, path, subsong FROM MediaLibrary WHERE [composers] IS NOT NULL
UNION ALL SELECT CASE WHEN [composersort] IS NOT NULL THEN 'composersort' END AS Node1, album, discnumber, tracknumber, path, subsong FROM MediaLibrary WHERE [composersort] IS NOT NULL
UNION ALL SELECT CASE WHEN [composersortorder] IS NOT NULL THEN 'composersortorder' END AS Node1, album, discnumber, tracknumber, path, subsong FROM MediaLibrary WHERE [composersortorder] IS NOT NULL
UNION ALL SELECT CASE WHEN [conductor] IS NOT NULL THEN 'conductor' END AS Node1, album, discnumber, tracknumber, path, subsong FROM MediaLibrary WHERE [conductor] IS NOT NULL
;

I made the changes but the sub-nodes won't add tracks to the playlist.  The parent node adds all the tracks, but not the sub-nodes.

[foo_uie_sql_tree] 21:01:40.488: Get tracks for query node "Bad Fields Forum Test" ...
[foo_uie_sql_tree] 21:01:40.492
>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
Query node SQL:

SELECT path,
       subsong
FROM BadFieldTable
ORDER BY Node1, album, discnumber, tracknumber COLLATE NaturalNoCase

<<<<<<<<<<<<<<<<<<<<<<<<<<<<<

[foo_uie_sql_tree] 21:01:40.800: Get tracks for query node "Bad Fields Forum Test": 0:00.312742

[foo_uie_sql_tree] 21:14:54.386: Get tracks for query node "Bad Fields Forum Test" ...
[foo_uie_sql_tree] 21:14:54.387
>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
Query node SQL:

SELECT path,
       subsong
FROM BadFieldTable
WHERE CAST(Node1 AS TEXT) = 'composers (67)'
ORDER BY Node1, album, discnumber, tracknumber COLLATE NaturalNoCase

<<<<<<<<<<<<<<<<<<<<<<<<<<<<<

[foo_uie_sql_tree] 21:14:54.387: Get tracks for query node "Bad Fields Forum Test": 0:00.002451

SELECT
  Node1 
FROM BadFieldTable
ORDER BY Node1, album, discnumber, tracknumber COLLATE NaturalNoCase

DROP TABLE IF EXISTS BadFieldTable;
CREATE TABLE BadFieldTable AS
WITH BadFields AS (
  SELECT
     composers,
     composersort,
     composersortorder,
     conductor,
     album, 
     discnumber, 
     tracknumber, 
     path,
     subsong 
 FROM MediaLibrary
 WHERE coalesce(composers, composersort, composersortorder, conductor) IS NOT NULL
)
SELECT
  fieldname || ' (' || count(fieldname) OVER(PARTITION BY fieldname) || ')' Node1,
  *
FROM (
  SELECT 
    'composers' fieldname, 
    *
  FROM BadFields
  WHERE composers IS NOT NULL
  UNION ALL
  SELECT 
    'composersort',
    *
  FROM BadFields
  WHERE composersort IS NOT NULL
  UNION ALL
  SELECT 'composersortorder', 
    *
  FROM BadFields
  WHERE composersortorder IS NOT NULL
  UNION ALL 
  SELECT 'conductor', 
    *
  FROM BadFields
  WHERE conductor IS NOT NULL
);

Unrelated Topic: In Playback Statistics, do I have to use Automatically Synchronize File Tags to use Played Count in SQL Tree?

There is no mention of it in the docs so I assume it's not currently possible, but it would be neat if one could use SQL query syntax to generate titles for target playlists.

Inserting entries into the Playlist table will result in adding items to the relevant playlists.
...
If the specified playlist doesn't exist, it will be created.

INSERT INTO PlaylistUpdatable(path,playlist_name)
SELECT '','New Playlist';

INSERT INTO PlaylistUpdatable(path,subsong,playlist_name)
SELECT path,subsong,'Motörhead'
FROM MediaLibrary
WHERE artist='Motörhead'