Skip to main content

Notice

Please note that most of the software linked on this forum is likely to be safe to use. If you are unsure, feel free to ask in the relevant topics, or send a private message to an administrator or moderator. To help curb the problems of false positives, or in the event that you do find actual malware, you can contribute through the article linked here.
Topic: [fb2k v2] SQL Tree (foo_uie_sql_tree) (Read 137935 times) previous topic - next topic
0 Members and 3 Guests are viewing this topic.

Re: foo_uie_sql_tree

Reply #125
On an unrelated note, I've been trying to wrap my head around the genre query in your example nodes. Could you direct me to where the tf() function is documented? Google leads me in several different directions, but "term frequency" seems to be the likeliest.
It is short for "title format". The foo_sqlite documentation contains a section on custom SQL functions.

Re: foo_uie_sql_tree

Reply #126
I'm trying to run some SQL-queries but somehow they don't seem to work properly, because the results are not grouped as they should (according to the query).
I'm not familiar with SQL queries so I tried some from Grimes

Here is one:
//Artist playcount
Code: [Select]
SELECT artist, round(avg(play_count),2) as playcount
FROM medialibrary
GROUP BY artist
ORDER BY playcount DESC
Because I'm using Playback statistics I get an error when I try to copy and execute this query, because
SQLite Error: no such column: play_count
So I changed the first instance of play_count to "%play_count%". But this didn't work (I thought the "as playcount" acts as a variable definition) so I changed all instances of play_count with "%play_count%".
This lets me execute the query, but it doesn't group by artist nor is it in any descending order.
Can someone help me with this?

Additionally, I want a query to list all Artists where I have more than x songs from. Any help?

Re: foo_uie_sql_tree

Reply #127
I want a query to list all Artists where I have more than x songs from. Any help?
Maybe something like this will do (replace 10 with the desired minimum number):
Code: [Select]
SELECT artist, count(*) AS songs
  FROM medialibrary
 GROUP BY artist
HAVING count(*) > 10

Re: foo_uie_sql_tree

Reply #128
I want a query to list all Artists where I have more than x songs from. Any help?
Maybe something like this will do (replace 10 with the desired minimum number):
Code: [Select]
SELECT artist, count(*) AS songs
  FROM medialibrary
 GROUP BY artist
HAVING count(*) > 10


Thank you, but I still get all my Songs shown as a result. So not even that works...

Re: foo_uie_sql_tree

Reply #129
Here is one:
In general, the query is correct. How is your %play_count% column defined for the MediaLibrary table (Preferences -> Media Library -> SQLite viewer -> MediaLibrary table)?


Additionally, I want a query to list all Artists where I have more than x songs from. Any help?
The query provided by mobyduck is also correct for this purpose. So could you please post some screen shots, which show, what you defined where?

Re: foo_uie_sql_tree

Reply #130
In general, the query is correct. How is your %play_count% column defined for the MediaLibrary table (Preferences -> Media Library -> SQLite viewer -> MediaLibrary table)?
I don't have such a column there. So I guess that's the problem. I added the following now:
Name: play_count
Type: TFExpression
Collation: NaturalNoCase(What's this?)
Title format expression: [%play_count%]

with this the "Artist playcount" query puts my most played song at the top of the playlist, but it is not grouped by artists. How do I make that happen?

Thank you already! I didn't know that you had to insert a column for play_count in the SQLite Viewer preferences.

Re: foo_uie_sql_tree

Reply #131
Collation: NaturalNoCase(What's this?)
It sorts the column in a natural, case insensitive order. This is actually not relevant for number only columns like play_count, but it's not doing any harm. For this case you could also use Binary, which could you save some milliseconds execution time.

with this the "Artist playcount" query puts my most played song at the top of the playlist
This is not possible. Queries, which contain aggregate functions like count() or avg() cannot be used for sending the result to a playlist. You would get an error message, if you try this. You can only send the result of such queries to the SQLite Viewer console.

Thank you already! I didn't know that you had to insert a column for play_count in the SQLite Viewer preferences.
It's because play_count is not a tag, which could be added automatically to the table columns during the first use of the component. If you tag your files later with tags, which you didn't use so far, you also need to add them manually or by pressing the button "Add defaults" in the preferences dialog.

As already asked, it would be helpful, if you could post some screen shots from your settings and your result.

Re: foo_uie_sql_tree

Reply #132
And before anyone asks, "natural" sorting means that it orders numbers by value, regardless of whether they happen to be padded to a fixed number of digits. Of course, this form of sorting tends to irk me sometimes, as it never takes into account hexadecimal numbering.

Re: foo_uie_sql_tree

Reply #133
Aaah, okay. When I sent the query to the SQLite Viewer Console it showed me the correct results.
But what kind of queries can I make to create a playlist? I thought I could use queries as a "smarter" and more elegant way to make autoplaylists.
I attached my preferences of the SQLite Viewer, and my query which now in the "Action" tab is changed "Send to SQLite Console".

Re: foo_uie_sql_tree

Reply #134
Thanks for the screen shots. Now it's more clear what you're doing. As you didn't added any sorting to the query, you didn't get an error. On the other hand, you need to sort the result, to get the desired order in the playlist.

But what kind of queries can I make to create a playlist?
You need to define queries in that way, that the result is one track per row. So, to get a playlist filled for the two cases mentioned in this thread, you could use the following queries:

Code: [Select]
SELECT 1
FROM MediaLibrary a JOIN
     (SELECT artist,avg(play_count) avg_play_count
      FROM MediaLibrary
      GROUP BY artist) b ON a.artist=b.artist
ORDER BY b.avg_play_count DESC, a.artist,album,discnumber,tracknumber

Code: [Select]
SELECT 1
FROM MediaLibrary a JOIN
     (SELECT artist,count(*) count_tracks
      FROM MediaLibrary
      GROUP BY artist) b ON a.artist=b.artist
WHERE count_tracks>10
ORDER BY b.count_tracks DESC, a.artist,album,discnumber,tracknumber

But be aware, that especially for large libraries, queries which are using the MediaLibrary table more than once, are quite slow.

To get it a bit faster, you could use a temporary table for the grouped subqueries. For the second query, you could add the following code to the batch field of your definition.

Code: [Select]
DROP TABLE IF EXISTS artist_count_tracks;
CREATE TEMPORARY TABLE artist_count_tracks AS
SELECT artist,count(*) count_tracks
FROM MediaLibrary
GROUP BY artist
HAVING count_tracks>10;

CREATE UNIQUE INDEX artist_count_tracks_index ON artist_count_tracks(artist);

The code for the query field, would look like this:
Code: [Select]
SELECT a.artist,b.count_tracks
FROM MediaLibrary a JOIN artist_count_tracks b ON b.artist=a.artist
ORDER BY b.count_tracks DESC, a.artist,album,discnumber,tracknumber

The predicate for joining both tables needs to be written as "b.artist=a.artist". If you change the order to "a.artist=b.artist", the index artist_count_tracks_index won't be used for some reason, which makes everything even slower.

I thought I could use queries as a "smarter" and more elegant way to make autoplaylists.
Actually, not. Autoplaylist are playlist, which are updating themselves, if the tags in the library change. This won't happen with playlists, which are fed by SQL Tree, because they are simply static playlist. Of course, sending the result again to the playlist, would update the playlist according to the current tags in the media library.

But you can indeed create playlists, which cannot be created with the standard components, which are using title format, like all queries, which build relations between tracks.

Edit: Fixed wrong SQL.

Re: foo_uie_sql_tree

Reply #135
You need to define queries in that way, that the result is one track per row. So, to get a playlist filled for the two cases mentioned in this thread, you could use the following queries:

Code: [Select]
SELECT 1
FROM MediaLibrary a JOIN
    (SELECT artist,avg(play_count) avg_play_count
      FROM MediaLibrary
      GROUP BY artist) b ON a.artist=b.artist
ORDER BY b.avg_play_count DESC, a.artist,album,discnumber,tracknumber

Code: [Select]
SELECT 1
FROM MediaLibrary a JOIN
    (SELECT artist,count(*) count_tracks
      FROM MediaLibrary
      GROUP BY artist) b ON a.artist=b.artist
WHERE count_tracks>10
ORDER BY b.count_tracks DESC, a.artist,album,discnumber,tracknumber

But be aware, that especially for large libraries, queries which are using the MediaLibrary table more than once, are quite slow.

Thank you very much, these are working well now!
But the next one doesn't.
To get it a bit faster, you could use a temporary table for the grouped subqueries. For the second query, you could add the following code to the batch field of your definition.

Code: [Select]
DROP TABLE IF EXISTS artist_count_tracks;
CREATE TEMPORARY TABLE artist_count_tracks AS
SELECT artist,count(*) count_tracks
FROM MediaLibrary
WHERE count_tracks>10
GROUP BY artist;

CREATE UNIQUE INDEX artist_count_tracks_index ON artist_count_tracks(artist);

When I add this to the batch field and click Validate/Execute I get the following Error:
Code: [Select]
Execution error:
SQLite Error: (1) misuse of aggregate: count()

Error while preparing the first statement of:
CREATE TEMPORARY TABLE artist_count_tracks AS
SELECT artist,count(*) count_tracks
FROM MediaLibrary
WHERE count_tracks>10
GROUP BY artist;

CREATE UNIQUE INDEX artist_count_tracks_index ON artist_count_tracks(artist);


The code for the query field, would look like this:
Code: [Select]
SELECT a.artist,b.count_tracks
FROM MediaLibrary a JOIN artist_count_tracks b ON b.artist=a.artist
ORDER BY b.count_tracks DESC, a.artist,album,discnumber,tracknumber
When I enter this as query I get the error "no_such_table: artist_count_tracks". Probably because the batch didn't work.

Re: foo_uie_sql_tree

Reply #136
I think you have to use the HAVING clause, but this seems more flexible to me:
Code: [Select]
DROP TABLE IF EXISTS artist_count_tracks;
CREATE TEMPORARY TABLE artist_count_tracks AS
SELECT artist,count(*) count_tracks
  FROM MediaLibrary
 GROUP BY artist;

CREATE UNIQUE INDEX artist_count_tracks_index ON artist_count_tracks(artist);
Code: [Select]
SELECT a.artist,b.count_tracks
  FROM MediaLibrary a
  JOIN artist_count_tracks b
    ON b.artist = a.artist
 WHERE b.count_tracks > 10
 ORDER BY b.count_tracks DESC, a.artist,album,discnumber,tracknumber
HTH.

Re: foo_uie_sql_tree

Reply #137
I think you have to use the HAVING clause,
Of course, I fixed it.
but this seems more flexible to me:
To be honest, I don't see a better flexibility with your solution, but it's definitely less efficient, although in many cases you don't really notice the difference. With your approach the temporary table is larger, which means it needs more disk space and it also needs more time to create the table, because more records need to be written. Additionally the subsequent SELECT extracts the data from more records, which also costs some more time.

Re: foo_uie_sql_tree

Reply #138
I don't see a better flexibility with your solution
Well, it is if you want to do multiple queries each with a different minimum number of tracks.

Also, disk space can become an issue if you have a *huge* number of artists below the threshold, otherwise I don't think you'll notice a real difference.

Finally, if you go to the extent of creating a temp table, it might be a good idea adding other aggregate information about each artist and make it even more generally useful.

Anyway, just my two cents...

Re: foo_uie_sql_tree

Reply #139
Hi guys,

I would like to sort my mp3 folders - no tracks- from most to less popular (using statistics or DAR). Is it possible?

Re: foo_uie_sql_tree

Reply #140
I created an account just to say I really appreciate this component and I'm a little confused on the usage of the "Refresh query, on load" function. I have a query that creates a short list of songs with a specific rating, and the action is set to replace the past query with the present. My understanding of the refresh on load is that it will rerun the query, but how can I make it automatically update the playlist without opening the tree and clicking on the node?

tl;dr how do I automatically create a playlist from a query when foobar starts up

Re: foo_uie_sql_tree

Reply #141
"Refresh on load" refreshes only the node query, it doesn't execute one of the node actions.

Re: foo_uie_sql_tree

Reply #142
"Refresh on load" refreshes only the node query, it doesn't execute one of the node actions.

Interesting. If that's the case, what would be the SQL syntax for creating or updating a playlist?

Re: foo_uie_sql_tree

Reply #143
It's documented in the help file from foo_sqlite (SQLite Viewer - MetaDb_Module).


Re: foo_uie_sql_tree

Reply #145
I keep getting "SQL Error:(1) table Playlist_Updateable already exists...Error while preparing the first statement of:.. " if I try to add the query to the Query tab of the node (it works in the Batch tab, but that doesn't seem to allow the Refresh Query on load feature to work). The query works through the SQLite console as well, but that doesn't help.

Code: [Select]
DROP TABLE IF EXISTS Playlist_Updatable;
CREATE VIRTUAL TABLE Playlist_Updatable USING MetaDB_Module(no_multivalue_split,playlist);
DELETE FROM Playlist_Updatable
WHERE playlist_name="Radio: Neil Young";
INSERT INTO Playlist_Updatable(metadb_handle,playlist_name)
SELECT metadb_handle,'Radio: Neil Young'
FROM MediaLibrary
WHERE "album artist"='Neil Young' AND (RATING >= 2 OR RATING IS NULL)
ORDER BY RANDOM()
LIMIT 25

What am I doing wrong?

Re: foo_uie_sql_tree

Reply #146
You cannot add a sequence of statements (= batch) to the query tab of the node, so the statements must be put into the batch section. Further "Refresh on load" does exactly the same as manually refreshing the node by using the context menu for this node and it works definitely also with "on load". So something else must be wrong. So, please post the complete settings for the node here.

By the way, do you get the expected result in the SQLite console with your query? If yes, it would mean, that the "album artist" tag itself is filled in addition to the artist tag. With "album artist" there is no automatic fallback to the artist tag, if the "album artist" tag is not set. To get this, you would need to use "%album artist%" instead.

And one other thing: I would move the dropping and creation of the virtual table to the batch section of a separate node, which is only executed on demand, not automatically on load. There is no need to drop and create the virtual table on every load.

Re: foo_uie_sql_tree

Reply #147
By the way, do you get the expected result in the SQLite console with your query?

Yes, it works in the console and in a node under the Batch tab (tested both with creating a separate node for the droping/creating the virtual table and both executed correctly). However, neither tests executed on load of Foobar, but they do work if I open the SQL tree view, even without clicking on anything it will update the playlists. The node uses all the default settings in the root pathway, except for enabling on load in the Advanced tab.

As another test, I created a new node with the Batch code (from the docs), and didn't activate/run it before closing foobar.
Code: [Select]
INSERT INTO Playlist_Updatable(metadb_handle)
SELECT metadb_handle
FROM MediaLibrary
WHERE artist='Metallica'

and I enabled on load from the advanced tab. No playlist was created on rebooting foobar. However, when I opened SQL tree from the context menu, without clicking anything, the playlist appeared.

Re: foo_uie_sql_tree

Reply #148
However, neither tests executed on load of Foobar, but they do work if I open the SQL tree view
Ok, that explains it. The "Refresh query, on load" can only work, it the tree view panel is opened. I need to update the documentation on this matter. So, unfortunately there is no way to get what you want, if the tree view panel is not opened.

Re: foo_uie_sql_tree

Reply #149
So, unfortunately there is no way to get what you want, if the tree view panel is not opened.

I found a workaround. Integrating a SQL Tree into a panel through columns UI will refresh on load properly :D