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: foo_uie_sql_tree (Read 69949 times) previous topic - next topic
0 Members and 1 Guest are viewing this topic.

Re: foo_uie_sql_tree

Reply #200
A cosmetic request for the panel. An edge style would be nice to have. Thanks.
It's already there (only for Columns UI). Right click in the relevant panel (not the one you can open from the menu, because it is DUI) and choose options.


Re: foo_uie_sql_tree

Reply #202
I have a requirement where I'd like to filter for all albums in one playlist that do not have album artist set correctly. Incorrect would be defined as the album having different track artists but an empty album artist (Album Artist could be either "various artists" or a real album artist.).

I was thinking of solving this by hashing numerically the artist for each grouping and than use an operation to determine if this is the same for each row. However I saw that sqlite has no such function built in.

Maybe there is an easier way of doing this? Many thanks in advance...

Re: foo_uie_sql_tree

Reply #203
In general you can start with this query:

Code: [Select]
SELECT artist,
       album
FROM MediaLibrary
WHERE (album, totaltracks) IN (SELECT album, totaltracks
                FROM MediaLibrary
                WHERE "album artist" IS NULL
                GROUP BY album, totaltracks
                HAVING COUNT(DISTINCT artist)>1)
ORDER BY artist, album

Depending on your library size and structure you might run into performance issues. In this case you need to write the result of the subquery to a temporary table , add an index on album to it and rewrite the query by joining MediaLibrary and the temporary table.

If the the tags album and totaltracks are not sufficient to uniquely identify an album you need ot adjust the combination of album and totaltracks according to your tagging scheme.

Re: foo_uie_sql_tree

Reply #204
Excuse me , I have a question need help very much . :'(
Edit the data in path
all playlist path : J:\ => I:\ (ex : J:\POP\A => I:\POP\A )

Quote
Execution error:
SQLite Error: (8) attempt to write a readonly database

Error while executing:
update Playlist
set path =
(
SELECT replace( path, 'J:\', 'I:\' ) path FROM Playlist
where path like 'J:\%'
)

Please help me :'(

Re: foo_uie_sql_tree

Reply #205
Unfortunately it is not possible, what you want to achieve. Besides the fact, that you are using a readonly version of the virtual playlist table (please consult the help file how to use a read/write version of it), you can only update tags in this way, but not file attributes. You might try the plugin foo_playlist_revive to get what you want.

Re: foo_uie_sql_tree

Reply #206
Unfortunately it is not possible, what you want to achieve. Besides the fact, that you are using a readonly version of the virtual playlist table (please consult the help file how to use a read/write version of it), you can only update tags in this way, but not file attributes. You might try the plugin foo_playlist_revive to get what you want.

I appreciate your help very much.  :-[
Now...music folders import ,  this process need to wait for a while.
Can I ask one more question?
Does the "Playlist name"  also have similar plug-ins to rename batches to replace keywords?
Quote
ex1:
Playlist name
[POP] AAA => [J-POP] AAA
[POP] BBB =>  [J-POP] BBB
[POP] CCC => [J-POP] CCC

ex2:
Playlist name
[C89] AAA => [C99] AAA
[C89] BBB =>  [C99] BBB
[C89] CCC => [C99] CCC
Originally wanted to use SQL to solve...but... :'(


Re: foo_uie_sql_tree

Reply #207
I'm not aware of any dedicated plugin, which is able to do this, but you could write a script for foo_spider_monkey_panel, which is doing what you want.

Re: foo_uie_sql_tree

Reply #208
I'm not aware of any dedicated plugin, which is able to do this, but you could write a script for foo_spider_monkey_panel, which is doing what you want.

Ok!! I will try to study it. Thank you very much for your help and answers today. O:)
Have a good one!

Re: foo_uie_sql_tree

Reply #209
I'm trying to get average playcount, but the fields just end up empty (except for the first one which shows the wrong value):



Which is weird because average rating works.

I'm using  a snippet that seems to have worked for some people years ago.

Edit: I found the solution. Playback Statistics data aren't normal tag fields, that's why in SQLite viewer settings, I had to change play_count from "Tag" to "TFExpression" and enter %play_count% in the text field so that it'd read the foo_playback_statistics data instead of Foobar's native play_count tag.

Re: foo_uie_sql_tree

Reply #210
This is most likely a problem with your data. You should try to use "CAST(play_count AS INT)" instead of just "play_count" for avg() as SQLite tries to identify the datatype of the field automatically. This doesn't always work as expected.

If this doesn't work, how is play_count defined for the MediaLibrary table and what is the result of the query (assuming that Absu has also play_count values)

SELECT artist,album, tracknumber, title, play_count
FROM MediaLibrary
WHERE artist IN ('Gates Of Ishtar','Absu')
ORDER BY 1,2,3;

Re: foo_uie_sql_tree

Reply #211
Thanks but see my edit, it was because the data isn't stored in a tag at all when you have foo_playcount installed.

Anyway, I have another problem: When I use "ORDER BY play_count DESC" and send the results to a playlist, their order is just tracks ordered by playcount with no regard for albums. I tried "ORDER BY avg(play_count)" but that gives me a syntax error. How can I make it so that the results in the playlist are grouped by album and albums ordered by average playcount? Sorry if that's a dumb question, it's my first time messing with SQL.

Re: foo_uie_sql_tree

Reply #212
This is a bit more complicated.

Put this into the Batch tab of your query node (not tested by myself):

Code: [Select]
DROP TABLE IF EXISTS tmp_artist_playcount;
CREATE TABLE tmp_artist_playcount AS
SELECT artist,avg(play_count) play_count
FROM MediaLibrary
GROUP BY artist;

CREATE UNIQUE INDEX tmp_artist_playcount_uq ON tmp_artist_playcount(artist);

and this into the Query tab (you might adjust the queried columns and the order to your needs):
Code: [Select]
SELECT ml.artist "Artist"
FROM MediaLibrary ml JOIN tmp_artist_playcount t ON ml.artist=t.artist
ORDER BY  play_count DESC, ml.artist, album, tracknumber

Re: foo_uie_sql_tree

Reply #213
Hi. I have a problem it seems. Queries are not populating any items in a tree.
I set "Click action: execute SQL"
but when I click it only briefly shows "database activity" and no elements in a tree are appearing after that.
(queries are working fine in SQLite console.)

Please help. Maybe i'm not using it the right way.

Foobar2000 1.6.5
SQLite viewer 1.1.0
SQL tree 2.0.3
Windows 10 x64

Re: foo_uie_sql_tree

Reply #214
Maybe i'm not using it the right way.
Yes  ;D, but the help needs to be more specific here.

ExecuteSQL is just executing the batch part of the node in the background, nothing more. If you want to have the tree populated without any further action, you don't need the click action at all. Just right click on the node and select "Refresh".

Re: foo_uie_sql_tree

Reply #215
ExecuteSQL is just executing the batch part of the node in the background, nothing more. If you want to have the tree populated without any further action, you don't need the click action at all. Just right click on the node and select "Refresh".
Refreshing it works! thank you for helping me out.
It was not clear for me that Refreshing will lead to building the tree. :)

EDIT: option "Refresh query: on use" is not triggering refresh on clicking. is it right? how can i trigger it?
On help page we read "whenever it is "used", i.e. expanding the node or clicking on the node". but i can't expand it when there is no "plus" button yet.
maybe this trigger works after nodes have been populated already and not when they are absent.

Re: foo_uie_sql_tree

Reply #216
option "Refresh query: on use" is not triggering refresh on clicking. is it right? how can i trigger it?
It will only be refreshed, if it could be needed, i.e. if the media library was changed since the last refresh in case the query is using the MediaLibrary table, or if the playlists were changed if the query is using the playlist table.

but i can't expand it when there is no "plus" button yet.
If this option is selected, the button for expanding the node is always shown, even if it has no items. But it looks like, that the button is only set either after a refresh or a program restart, but not by just selecting the relevant option.

Re: foo_uie_sql_tree

Reply #217
But it looks like, that the button is only set either after a refresh or a program restart, but not by just selecting the relevant option.
Yes! I had to reopen/restart SLQ tree to make "plus" button show up. Thank you for all clarifications. :)

 
SimplePortal 1.0.0 RC1 © 2008-2021