[fb2k v2] SQL Tree (foo_uie_sql_tree)

Topic: [fb2k v2] SQL Tree (foo_uie_sql_tree) (Read 149852 times) previous topic - next topic

0 Members and 1 Guest are viewing this topic.

Re: [fb2k v2] SQL Tree (foo_uie_sql_tree)

Reply #375 – 2024-03-16 18:39:00

Quote from: chrisdukes on 2024-03-16 14:27:38

I would really appreciate any advice how to make it simpler or faster.

Simpler: Probably not possible. SQL queries are tending to become quite complicated, if special conditions need to be fulfilled like here. Faster: Optimizing SQL queries can in general only be done based on the size and the content of the underlying data. But for sure the "ORDER BY" which is used for the creating of the MediaTable just decreases the performance without having any use. Furthermore at least creating an index on the "album artist" column of the MediaTable could be helpful.

Re: [fb2k v2] SQL Tree (foo_uie_sql_tree)

Reply #376 – 2024-03-16 19:33:29

I'm going to take this as a compliment.

Quote from: fbuser on 2024-03-16 18:39:00

Simpler: Probably not possible

Regarding the ORDER BY used to create MediaTable, I added that because I had a problem with track numbers in the playlist, albums in the playlist would be sorted like this:

Code: [Select]

1, 10, 11, 12...19, 2, 20, 21, 22...29, etc.

I had a really hard time fixing it until I added that ORDER BY statement in the MediaTable creation. I assume it had something to do with tracks for albums appearing twice in the query results. It would happen for most albums, but not all, and the problem wasn't the tags since they looked okay in MP3Tag. If you have some ideas that fix the problem, I'd love to hear them.

I created an album artist index for MediaTable and the results are faster.

Code: [Select]

CREATE INDEX AlbumArtistIndex ON MediaTable ([album artist]);

Thanks for the advice!

Re: [fb2k v2] SQL Tree (foo_uie_sql_tree)

Reply #377 – 2024-03-16 21:32:48

Quote from: chrisdukes on 2024-03-16 19:33:29

Regarding the ORDER BY used to create MediaTable, I added that because I had a problem with track numbers in the playlist, albums in the playlist would be sorted like this:
Code: [Select]
1, 10, 11, 12...19, 2, 20, 21, 22...29, etc.

It's because all columns are considered as text values by default. One possibility to fix this is to query the tracknumber as

Code: [Select]

(CAST(tracknumber AS INTEGER) tracknumber

when creating the MediaTable.

Quote from: chrisdukes on 2024-03-16 19:33:29

I had a really hard time fixing it until I added that ORDER BY statement in the MediaTable creation.

This was only by coincidence. An ORDER BY affects only the final query. There is no predictable order in which rows are stored in a table nor is there one when querying a table without an ORDER BY.

Quote from: chrisdukes on 2024-03-16 19:33:29

I assume it had something to do with tracks for albums appearing twice in the query results.

This happens because you still have genre_mv in the query which creates the MediaTable. Just drop it.

Re: [fb2k v2] SQL Tree (foo_uie_sql_tree)

Reply #378 – 2024-03-17 02:39:39

I removed the genre_mv and the ORDER BY statement and I added the CAST function, but the track numbers are in a random order now, not 1, 10, 11, 12...2, 20, etc, like before. Also, I understand that ORDER BY is useless in the CREATE TABLE, but for whatever reason, the tracknumbers sort correctly when I add it back in.

Code: [Select]

DROP TABLE IF EXISTS MediaTable;
CREATE TABLE MediaTable AS
  SELECT

    path,
    subsong,
    genre,
--  genre_mv,
    multivaluegenre2jsonarray(genre) genresplit,
    grouping,
    [original album],
    [original title],
    [album artist],
    album,
    discnumber,
    CAST(tracknumber AS INTEGER) tracknumber

  FROM MediaLibrary
  WHERE genre LIKE '%Score%' OR genre LIKE '%Stage & Screen%'
-- ORDER BY album, discnumber, tracknumber
;

Re: [fb2k v2] SQL Tree (foo_uie_sql_tree)

Reply #379 – 2024-03-17 06:56:08

Quote from: chrisdukes on 2024-03-17 02:39:39

I removed the genre_mv and the ORDER BY statement and I added the CAST function, but the track numbers are in a random order now,

Seems that I forgot to mention, that you have to move the ORDER BY of the table creation to the final query.

Re: [fb2k v2] SQL Tree (foo_uie_sql_tree)

Reply #380 – 2024-03-17 13:08:08

Well, hot damn! That worked! I used to have the ORDER BY at the bottom of the query, but I was still getting the 1, 10, 11, etc. track number order. So, I guess it was something else that was causing the problem.

Thanks for helping me fix it the right way!

Re: [fb2k v2] SQL Tree (foo_uie_sql_tree)

Reply #381 – 2024-03-18 00:21:33

I ran into a problem with track numbers that have letters; I have a lot of vinyl rips that often use A1, or AA3, etc in the tracknumber. The CAST(tracknumber AS INTEGER) function worked great for the number only track numbers, but track numbers with letters and double digits sort like this, A1, A10, A11, A12...B1, B10, B11, B12, etc.

So, I changed my first CREATE TABLE to this and it works, but I was wondering if you know a better way:

Code: [Select]

DROP TABLE IF EXISTS MediaTable;
CREATE TABLE MediaTable AS
SELECT

path,
subsong,
genre,
multivaluegenre2jsonarray(genre) genresplit,
grouping,
[original album],
[original title],
[album artist],
album,
discnumber,

CASE
WHEN tracknumber LIKE '%0%' THEN substr(tracknumber, 1, instr(tracknumber, '0') - 1) || printf('%02d', substr(tracknumber, instr(tracknumber, '0')))
WHEN tracknumber LIKE '%1%' THEN substr(tracknumber, 1, instr(tracknumber, '1') - 1) || printf('%02d', substr(tracknumber, instr(tracknumber, '1')))
WHEN tracknumber LIKE '%2%' THEN substr(tracknumber, 1, instr(tracknumber, '2') - 1) || printf('%02d', substr(tracknumber, instr(tracknumber, '2')))
WHEN tracknumber LIKE '%3%' THEN substr(tracknumber, 1, instr(tracknumber, '3') - 1) || printf('%02d', substr(tracknumber, instr(tracknumber, '3')))
WHEN tracknumber LIKE '%4%' THEN substr(tracknumber, 1, instr(tracknumber, '4') - 1) || printf('%02d', substr(tracknumber, instr(tracknumber, '4')))
WHEN tracknumber LIKE '%5%' THEN substr(tracknumber, 1, instr(tracknumber, '5') - 1) || printf('%02d', substr(tracknumber, instr(tracknumber, '5')))
WHEN tracknumber LIKE '%6%' THEN substr(tracknumber, 1, instr(tracknumber, '6') - 1) || printf('%02d', substr(tracknumber, instr(tracknumber, '6')))
WHEN tracknumber LIKE '%7%' THEN substr(tracknumber, 1, instr(tracknumber, '7') - 1) || printf('%02d', substr(tracknumber, instr(tracknumber, '7')))
WHEN tracknumber LIKE '%8%' THEN substr(tracknumber, 1, instr(tracknumber, '8') - 1) || printf('%02d', substr(tracknumber, instr(tracknumber, '8')))
WHEN tracknumber LIKE '%9%' THEN substr(tracknumber, 1, instr(tracknumber, '9') - 1) || printf('%02d', substr(tracknumber, instr(tracknumber, '9')))
ELSE CAST(tracknumber AS INTEGER)
END AS tracknumber_sorted

FROM MediaLibrary
WHERE genre LIKE '%Score%' OR genre LIKE '%Stage & Screen%';

Re: [fb2k v2] SQL Tree (foo_uie_sql_tree)

Reply #382 – 2024-03-19 21:04:54

Quote from: chrisdukes on 2024-03-18 00:21:33

but I was wondering if you know a better way:

Yes, it is.

Instead of casting the tracknumber to integer while creating the MediaTable, just select it as normal and change the ORDER BY of the final query for the tracknumber as follows:

Code: [Select]

ORDER BY
 <column1>,
 <column2>,
  ....
  tracknumber COLLATE NaturalNoCase

Note: The collation NaturalNoCase is a custom collation provided by foo_sqlite.

Re: [fb2k v2] SQL Tree (foo_uie_sql_tree)

Reply #383 – 2024-03-20 00:15:24

Nailed it!

Re: [fb2k v2] SQL Tree (foo_uie_sql_tree)

Reply #384 – 2024-03-23 20:49:53

Sorry, I have never worked with SQL queries so I'm struggling, but it seems like this component is the only way to achieve my goal.

I would like to create two lists, both with an Artist -> Album tree hierarchy, sorted by %last played%.

In the first list I would like to only include albums that have an average playcount of 3 or less.
In the second list I would like to only include albums that have an average playcount of 3 or more.

I got this far:

Code: [Select]

SELECT artist, album ||' ['||(avg(playcount * 100)/100)||']'
 FROM medialibrary
GROUP BY album
ORDER BY lastplayed

Can someone help me out?

Re: [fb2k v2] SQL Tree (foo_uie_sql_tree)

Reply #385 – 2024-03-24 17:03:45

Quote from: dpc666 on 2024-03-23 20:49:53

Sorry, I have never worked with SQL queries so I'm struggling, but it seems like this component is the only way to achieve my goal.

I would like to create two lists, both with an Artist -> Album tree hierarchy, sorted by %last played%.

In the first list I would like to only include albums that have an average playcount of 3 or less.
In the second list I would like to only include albums that have an average playcount of 3 or more.

I got this far:

Code: [Select]
SELECT artist, album ||' ['||(avg(playcount * 100)/100)||']'
 FROM medialibrary
GROUP BY album
ORDER BY lastplayed
Can someone help me out?

I switched to editing the example query instead of doing my own from scratch. This is where I'm at:

Code: [Select]

SELECT stdartist album_artist,
       '['||(avg(playcount * 100)/100)||'] '||album album_col,
        (CASE WHEN discnumber THEN 'Disc '||discnumber ELSE NULL END) disc_number
FROM MediaLibrary
GROUP BY 1,album
ORDER BY ifnull([album artist],artist) COLLATE NaturalNoCase,'['||max(lastplayed)||'] '||album desc,discnumber

Current issues:
- When I set "send to playlist" as action, I get "misuse of aggregate" errors for max() and avg() when clicking subnodes, seemingly because the node query autogenerated by the component doesn't use GROUP BY.
- I don't know how to filter albums by average.
- I don't know how to display "last played" in the artist nodes without splitting them into multiple nodes.

Re: [fb2k v2] SQL Tree (foo_uie_sql_tree)

Reply #386 – 2024-03-24 18:22:14

The query below should do more or less what you want. You need to use window functions instead of normal aggregate functions. Otherwise you would lose path and subsong which are required for sending the query result to a playlist.

Code: [Select]

WITH Albums AS (
  SELECT a.*,
         avg(CAST(playcount AS INTEGER)) OVER wAlbum playcount,
         max(lastplayed) OVER wArtist lastplayed
  FROM (
    SELECT path,
           subsong,
           playcount,
           lastplayed,
           ifnull("album artist",artist) album_artist,
           album,
           (CASE WHEN discnumber THEN 'Disc '||discnumber ELSE NULL END) disc_number
    FROM MediaLibrary
  ) a
  WINDOW wAlbum AS (PARTITION BY album_artist,album),
         wArtist AS (PARTITION BY album_artist)
)
SELECT '['||lastplayed||'] '||album_artist,
       '['||CAST(playcount AS TEXT)||'] '||album,
       disc_number
FROM Albums
ORDER BY lastplayed,album_artist,album,disc_number

Re: [fb2k v2] SQL Tree (foo_uie_sql_tree)

Reply #387 – 2024-03-24 21:07:50

Quote from: fbuser on 2024-03-24 18:22:14

Code: [Select]

WITH Albums AS (
  SELECT a.*,
         avg(CAST(playcount AS INTEGER)) OVER wAlbum playcount,
         max(lastplayed) OVER wArtist lastplayed
  FROM (
    SELECT path,
           subsong,
           playcount,
           lastplayed,
           ifnull("album artist",artist) album_artist,
           album,
           (CASE WHEN discnumber THEN 'Disc '||discnumber ELSE NULL END) disc_number
    FROM MediaLibrary
  ) a
  WINDOW wAlbum AS (PARTITION BY album_artist,album),
         wArtist AS (PARTITION BY album_artist)
)
SELECT '['||lastplayed||'] '||album_artist,
       '['||CAST(playcount AS TEXT)||'] '||album,
       disc_number
FROM Albums
ORDER BY lastplayed,album_artist,album,disc_number

This is extremely helpful, thank you!

Artist nodes are still split based on last played tho; for every track it seems.

Re: [fb2k v2] SQL Tree (foo_uie_sql_tree)

Reply #388 – 2024-03-24 22:14:02

Quote from: dpc666 on 2024-03-24 21:07:50

Artist nodes are still split based on last played tho; for every track it seems.

I'm not really sure what you mean. From my understanding of your statement it would not be possible with the provided query. Could you please therefore post a screen shot, which shows how your tree looks like?

Re: [fb2k v2] SQL Tree (foo_uie_sql_tree)

Reply #389 – 2024-03-25 11:40:42

Quote from: fbuser on 2024-03-24 22:14:02

Quote from: dpc666 on 2024-03-24 21:07:50
Artist nodes are still split based on last played tho; for every track it seems.
I'm not really sure what you mean. From my understanding of your statement it would not be possible with the provided query. Could you please therefore post a screen shot, which shows how your tree looks like?

Yeah ofc. Each of these nodes only contains one track. What I would like to happen is that only the most recent played date of the entire artist's discography gets displayed.

Re: [fb2k v2] SQL Tree (foo_uie_sql_tree)

Reply #390 – 2024-03-25 22:40:25

Quote from: dpc666 on 2024-03-25 11:40:42

What I would like to happen is that only the most recent played date of the entire artist's discography gets displayed.

Ok, that's exactly what the query I posted is doing. So my only guess is that you made a mistake while transferring my posted query to your tree. Could you please also post the query which you are using?

Re: [fb2k v2] SQL Tree (foo_uie_sql_tree)

Reply #391 – 2024-03-26 12:00:10

Quote from: fbuser on 2024-03-25 22:40:25

Quote from: dpc666 on 2024-03-25 11:40:42
What I would like to happen is that only the most recent played date of the entire artist's discography gets displayed.
Ok, that's exactly what the query I posted is doing. So my only guess is that you made a mistake while transferring my posted query to your tree. Could you please also post the query which you are using?

I simply copy-pasted it.

Code: [Select]

WITH Albums AS (
  SELECT a.*,
         avg(CAST(playcount AS INTEGER)) OVER wAlbum playcount,
         max(lastplayed) OVER wArtist lastplayed
  FROM (
    SELECT path,
           subsong,
           playcount,
           lastplayed,
           ifnull("album artist",artist) album_artist,
           album,
           (CASE WHEN discnumber THEN 'Disc '||discnumber ELSE NULL END) disc_number
    FROM MediaLibrary
  ) a
  WINDOW wAlbum AS (PARTITION BY album_artist,album),
         wArtist AS (PARTITION BY album_artist)
)
SELECT '['||lastplayed||'] '||album_artist,
       '['||CAST(playcount AS TEXT)||'] '||album,
       disc_number
FROM Albums
ORDER BY lastplayed,album_artist,album,disc_number

playcount and lastplayed use TFExpressions (Text) %play_count% and %last_played%, respectively, if that is relevant.

It doesn't seem like that should make a difference to anything, but I'm running Foobar2000 through Wine on Linux.

Re: [fb2k v2] SQL Tree (foo_uie_sql_tree)

Reply #392 – 2024-03-26 22:11:05

Ok, it seems there is somewhere a bug, when using a column alias with the same name as an existing column at least for the used query. This is normally not a problem.

It would require a lot of time to analyze this problem completely, but fortunately there is an easy workaround (see query below). I also adjusted the ORDER BY so that album will be sent to the playlist with the correct track order.

Actually, when I composed the query I used implicitely this workaround as I don't have playcount and lastplayed available in my application setup.

Code: [Select]

WITH Albums AS (
  SELECT a.*,
         avg(CAST(playcount AS INTEGER)) OVER wAlbum avg_playcount,
         max(lastplayed) OVER wArtist max_lastplayed
  FROM (
    SELECT path,
           subsong,
           playcount,
           lastplayed,
           ifnull("album artist",artist) album_artist,
           album,
           (CASE WHEN discnumber THEN 'Disc '||discnumber ELSE NULL END) disc_number,
           tracknumber
    FROM MediaLibrary
  ) a
  WINDOW wAlbum AS (PARTITION BY album_artist,album),
         wArtist AS (PARTITION BY album_artist)
)
SELECT '['||max_lastplayed||'] '||album_artist,
       '['||CAST(avg_playcount AS TEXT)||'] '||album,
       disc_number
FROM Albums
ORDER BY max_lastplayed,album_artist,avg_playcount,album,disc_number,tracknumber COLLATE NaturalNoCase

Re: [fb2k v2] SQL Tree (foo_uie_sql_tree)

Reply #393 – 2024-03-27 10:28:14

Quote from: fbuser on 2024-03-26 22:11:05

Code: [Select]

WITH Albums AS (
  SELECT a.*,
         avg(CAST(playcount AS INTEGER)) OVER wAlbum avg_playcount,
         max(lastplayed) OVER wArtist max_lastplayed
  FROM (
    SELECT path,
           subsong,
           playcount,
           lastplayed,
           ifnull("album artist",artist) album_artist,
           album,
           (CASE WHEN discnumber THEN 'Disc '||discnumber ELSE NULL END) disc_number,
           tracknumber
    FROM MediaLibrary
  ) a
  WINDOW wAlbum AS (PARTITION BY album_artist,album),
         wArtist AS (PARTITION BY album_artist)
)
SELECT '['||max_lastplayed||'] '||album_artist,
       '['||CAST(avg_playcount AS TEXT)||'] '||album,
       disc_number
FROM Albums
ORDER BY max_lastplayed,album_artist,avg_playcount,album,disc_number,tracknumber COLLATE NaturalNoCase

This works perfectly, thanks!

Re: [fb2k v2] SQL Tree (foo_uie_sql_tree)

Reply #394 – 2024-03-27 10:33:43

The only remaining issue is that it seems that when an artist has tracks that have never been played, this gets treated as most recent and displayed as "[N/A]".

Edit: Okay, I solved this by putting [] around %last_played%. Kinda obvious ngl.

Edit2: Apparently, this is how I can filter by average plays (second to last line):

Code: [Select]

WITH Albums AS (
  SELECT a.*,
         avg(CAST(playcount AS INTEGER)) OVER wAlbum avg_playcount,
         max(lastplayed) OVER wArtist max_lastplayed
  FROM (
    SELECT path,
           subsong,
           playcount,
           lastplayed,
           stdartist album_artist,
           album,
           (CASE WHEN discnumber THEN 'Disc '||discnumber ELSE NULL END) disc_number,
           tracknumber
    FROM MediaLibrary
  ) a
  WINDOW wAlbum AS (PARTITION BY album_artist,album),
         wArtist AS (PARTITION BY album_artist)
)
SELECT '['||max_lastplayed||'] '||album_artist,
       '['||CAST(avg_playcount AS TEXT)||'] '||album,
       disc_number
FROM Albums
WHERE avg_playcount > 4
ORDER BY max_lastplayed,album_artist,avg_playcount,album,disc_number,tracknumber COLLATE NaturalNoCase

Re: [fb2k v2] SQL Tree (foo_uie_sql_tree)

Reply #395 – 2024-03-27 11:02:01

It seems a little strange that there is no "click to refresh" action tbh, i.e. build the tree without sending anything to a playlist.

Re: [fb2k v2] SQL Tree (foo_uie_sql_tree)

Reply #396 – 2024-03-27 18:07:43

Quote from: dpc666 on 2024-03-27 11:02:01

It seems a little strange that there is no "click to refresh" action tbh, i.e. build the tree without sending anything to a playlist.

You can do this with the context menu (Refresh) for the relevant node or for all nodes which have "Include in refreshing all nodes" activated with the tree context menu (Refresh all).

Re: [fb2k v2] SQL Tree (foo_uie_sql_tree)

Reply #397 – 2024-03-30 00:29:27

I think I don't understand the difference between the Batch tab and the Query tab. ¯\_(ツ)_/¯

Can you refresh if there's only a query in the Batch tab?
Can you only refresh a tree if there's a query?
Does refreshing a tree also recreate a table like executing the query in SQL Console?

If I create TableA in one tree, then I create TableB from Table A in a separate tree, how can I automatically refresh Table A before Table B when I load Foobar2000? I guess I would want to do the same thing for Refresh All. I have more tables that are dependent on TableA, but it doesn't matter what order they're refreshed in as long as TableA is refreshed first.

Thanks again for the awesome plug-in!

Re: [fb2k v2] SQL Tree (foo_uie_sql_tree)

Reply #398 – 2024-03-31 17:46:50

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

I think I don't understand the difference between the Batch tab and the Query tab.

The batch tab is mainly for creating temporary tables to serve the relevant query.

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

Can you refresh if there's only a query in the Batch tab?

Refreshing a tree node first executes the batch part of this node and then executes the query part. It is no problem, if only one of them is present.

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?

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

Does refreshing a tree also recreate a table like executing the query in SQL Console?

Yes.

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

If I create TableA in one tree, then I create TableB from Table A in a separate tree, how can I automatically refresh Table A before Table B when I load Foobar2000?

The order of refreshing trees is undefined. If you actually mean tree nodes the order of refreshing tree nodes is from top to bottom of the tree.

Re: [fb2k v2] SQL Tree (foo_uie_sql_tree)

Reply #399 – 2024-03-31 21:41:43

I have one (hopefully last) urgent question before I can start using the setup daily. I want to eventually learn SQL query syntax to refine the behavior but I'm not there yet.

When calculating the average playcount for every album, how do I ignore tracks with playcount 0? I.e. only calculate the average from tracks that have actually been played? I don't wanna exclude them from the resulting playlists, just from the calculation for the average that gets displayed. Some albums have tracks where the stats are lost because of database errors or somesuch, and other albums have tracks that I usually skip. I don't want those to factor into the average because it leads to misleading averages for my purposes.

Notice