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] Random Pools (foo_random_pools) (Read 181821 times) previous topic - next topic
0 Members and 1 Guest are viewing this topic.

Re: [fb2k v2] Random Pools (foo_random_pools)

Reply #375
One more thing to tackle, how to not play recent songs

I fixed that by putting this in each pool filter... "(%path% HAS "E:\Music\Brian Music\Albums") AND ((NOT %last_played% DURING LAST 90 DAYS))"

This plugin changes my life, love it! Let me know how to send you money!

Re: [fb2k v2] Random Pools (foo_random_pools)

Reply #376
Hello, I've just found out about this plugin and I have a few questions as to how to utilise these fields...

Firstly the Group field inside the pool's properties... what does this determine?
I've set it to %all% and it's working as expected. But I still don't understand what it's used for or how to use it properly.

Secondly, is it possible to prevent songs from the same album from showing up? I want it something similar to Foobar's shuffle tracks.

I've attached below my pool settings.

Re: [fb2k v2] Random Pools (foo_random_pools)

Reply #377
Firstly the Group field inside the pool's properties... what does this determine?
It picks a random group with the same values for the specified title format expression from the set of tracks which you define under "Source / Filter" on the right side of the

I've set it to %all% and it's working as expected.
As long as you don't use a tag with the name "all" the title format value will be ? for all tracks, so all tracks are just one group and will be picked completely. If you would set the group to %album% only the tracks of only one randomly selected album would be picked from the set of tracks from the source. If you limit the set of tracks from the source to your needs, %all% (or any anoter not existing tag) is a good choice.

Secondly, is it possible to prevent songs from the same album from showing up?
Yes, change your setting for example as follows to get 20 random tracks from 20 random albums:

WITH
Code: [Select]
CTE AS (
  SELECT DISTINCT
         first_value(path) OVER w_album path,
         first_value(subsong) OVER w_album subsong
  FROM MediaLibrary
  WINDOW w_album AS (PARTITION BY album ORDER BY random())
)

ORDER BY
Code: [Select]
random()

LIMIT
Code: [Select]
20

Re: [fb2k v2] Random Pools (foo_random_pools)

Reply #378
Just a small doubt above, I already have a specific SQL query for Random Pools, if I understood the SQLite code properly at the ORDER BY random(), it will entirely randomise my result and not get what I currently have as my SQLite query in this pool?

Re: [fb2k v2] Random Pools (foo_random_pools)

Reply #379
Just a small doubt above, I already have a specific SQL query for Random Pools, if I understood the SQLite code properly at the ORDER BY random(), it will entirely randomise my result and not get what I currently have as my SQLite query in this pool?
No, but first of all the CTE above was not correct for the described case (a DISTINCT was missing). I updated it.

Now let's have closer looks at your first pool definition with %all% as group and your second pool definition %album% as group:

In both cases the definitions on the right side define the set of tracks which is used to get one random group from this set.

First case (group = %all%)

With the CTE tracks of the media library are reduced to one random track of each album. All other tracks are filtered out. By using the WINDOW definition
Code: [Select]
WINDOW w_album AS (PARTITION BY album ORDER BY random())
the tracks of the media library are virtually partitioned into albums having all tracks of them sorted randomly each.

With the window function first_value() you select from each of these partitions (=album) path and subsong of the first track:
Code: [Select]
first_value(path) OVER w_album path,
first_value(subsong) OVER w_album subsong

As the tracks are sorted randomly for each partition you get an arbitrary track of each album with first_value(). The added DISTINCT eliminates all duplicates as first_value() will be applied to every track of an album with the same result.

Now the set of tracks is reduced to one random track per album. With the ORDER BY
Code: [Select]
random()
these tracks are sorted randomly and with LIMIT
Code: [Select]
20
the first 20 tracks of the randomly sorted tracks are picked. So, the final set of tracks contains 20 random tracks from 20 different albums.

These 20 tracks are now used to pick randomly a group as defined on the left side of the pool definition. As %all% is not an existing tag the title format result is ? for all tracks, so all the tracks are just one single group and they will be used completely.

Second case (group = %album%)

First, the CTE is useless and can be removed. Instead just use "MediaLibrary" instead of "CTE" for "SELECT path, subsong FROM". At this point the set of tracks to pick a random group from is the whole media library.

With the specified where clause you filter out all tracks which were played in the last 5 hours or which are not loved.

The remaining tracks are sorted by the last played timestamp in ascending order (ORDER BY) and with the limit 2 you reduce the remaining tracks just the two tracks, which were played the longest time ago.

From these two tracks you pick either both tracks, if they have the same value for %album% or randomly one of them if their value for %album% is different.

This is for sure not what you want  :)

Re: [fb2k v2] Random Pools (foo_random_pools)

Reply #380
Sorry, I failed to understand exactly for the Second Case... just so that we are on the same page I've made two pools one as Least Recently Played and the other as Lowest Play Count, I've shared my settings for Least Recently Played and I wanted to know if it's possible to combine the query you suggested to prevent tracks from the same album playing...

For the group should I change it to %track%? or what would be the best scenario here to set for the Group parameter?

I'll elaborate my Pool settings here as well

Code: [Select]
Name: Least Recently Played
Group: %album%
Determine probability for picking groups based on group size: Untick
Number of groups to add: 1
Number of tracks to add: 0
Number of minutes to add: 0
Number of MBs to add: 0
Limit after sorting: Untick
Sort: Empty
Sort / limit each group: Empty
Source / Filter
Type: SQL (requires foo_sqlite)
Query
WITH: CTE AS(
 SELECT path, subsong, [%jsp3_loved%], COALESCE(NULLIF([%jsp3_last_played%], ''), '1970-01-01 00:00:00') AS [%jsp3_last_played%]
 FROM MediaLibrary
)

SELECT path, subsong FROM: CTE

WHERE: [%jsp3_last_played%] < datetime('now', '-300 minutes')
AND [%jsp3_loved%]

ORDER BY: [%jsp3_last_played%]

LIMIT: 2

Filter with the content of the target playlist: Tick
Filter with the content of the target playlist using a title format expression: Empty
Priority: 1

You mentioned not to use CTE as well, but I found out that if I don't include that in "SELECT path, subsong FROM" my COALESCE doesn't take place and replace the NULL values inside the database.

Re: [fb2k v2] Random Pools (foo_random_pools)

Reply #381
Sorry, I failed to understand exactly for the Second Case... just so that we are on the same page I've made two pools one as Least Recently Played and the other as Lowest Play Count, I've shared my settings for Least Recently Played and I wanted to know if it's possible to combine the query you suggested to prevent tracks from the same album playing...
Definitely not easily. It will become quite complex and probably you will need to create temporary tables in the batch part of the sql definition at least for performance reasons.

For the group should I change it to %track%? or what would be the best scenario here to set for the Group parameter?
No. Besides that you probably meant %title%, it would only rule out, that you get both tracks together from the set of tracks, which you defined with your SQL (as long as %title% for both tracks is different).

You mentioned not to use CTE as well, but I found out that if I don't include that in "SELECT path, subsong FROM" my COALESCE doesn't take place and replace the NULL values inside the database.
Ok, I didn't see the CTE completely, but you still don't need it by setting WHERE to
Code: [Select]
COALESCE(NULLIF([%jsp3_last_played%], ''), '1970-01-01 00:00:00') < datetime('now', '-300 minutes')
AND [%jsp3_loved%]

Re: [fb2k v2] Random Pools (foo_random_pools)

Reply #382
Definitely not easily. It will become quite complex and probably you will need to create temporary tables in the batch part of the sql definition at least for performance reasons.
Ah, guess it's not worth the trouble...? I'm not sure how feasible it is as I'm still a beginner with SQL queries...

Temp tables in batch part just so that the results are randomised? and then in the query part apply my filter conditions?

No. Besides that you probably meant %title%, it would only rule out, that you get both tracks together from the set of tracks, which you defined with your SQL (as long as %title% for both tracks is different).

I have some tracks which have the same title but from different albums, to rule out this confusion would it be better to have the Group as %path% - %subsong%

Re: [fb2k v2] Random Pools (foo_random_pools)

Reply #383
Ah, guess it's not worth the trouble...?
Maybe not. Another problem is, that your SQL reduces the set of tracks already to only two tracks: The two least recently played tracks. From these two tracks you try got get one randomly. Is this really what you want? How many tracks do you actually want to get?

I'm not sure how feasible it is as I'm still a beginner with SQL queries...
It could need several hours to get the wanted result. You should first define exactly the result, that you want to get, without thinking how to implement it. Actually, it is not really clear for me what it is.

Temp tables in batch part just so that the results are randomised? and then in the query part apply my filter conditions?
No, the temp tables needs to be used to simplify the task (and maybe also for performance reasons). Although I have no concrete idea to solve the problem, you will probably end up in deeply nested subselects, if you don't use temp tables.

I have some tracks which have the same title but from different albums, to rule out this confusion would it be better to have the Group as %path% - %subsong%
Probably not. This would get you always only one track independent from the number of tracks in the set of tracks defined with the SQL.

Re: [fb2k v2] Random Pools (foo_random_pools)

Reply #384
So I've created a really rough Batch and Query SQL queries with basic understanding but could you review it?

Code: [Select]
Batch:
DROP TABLE IF EXISTS tmpRandomLRP;

CREATE TEMPORARY TABLE tmpRandomLRP AS
SELECT DISTINCT
    path,
    subsong,
    [%jsp3_loved%],
    COALESCE(NULLIF([%jsp3_last_played%], ''), '1970-01-01 00:00:00') AS [%jsp3_last_played%]
FROM (
    SELECT
        first_value(path) OVER w_album path,
        first_value(subsong) OVER w_album subsong,
        [%jsp3_loved%],
        COALESCE(NULLIF([%jsp3_last_played%], ''), '1970-01-01 00:00:00') AS [%jsp3_last_played%]
    FROM MediaLibrary
    WINDOW w_album AS (PARTITION BY album ORDER BY random())
) AS subquery
ORDER BY random();

Code: [Select]
Query:
WITH CTE AS(
 SELECT path, subsong, [%jsp3_loved%], COALESCE(NULLIF([%jsp3_last_played%], ''), '1970-01-01 00:00:00') AS [%jsp3_last_played%]
 FROM tmpRandomLRP
)
SELECT path,
       subsong
FROM CTE
WHERE ([%jsp3_loved%]) AND ([%jsp3_last_played%] < datetime('now', '-300 minutes'))
ORDER BY [%jsp3_last_played%]
LIMIT 2

Grouping is still set to %album%, should I switch it back to %all% or not touch it for now?

Re: [fb2k v2] Random Pools (foo_random_pools)

Reply #385
So I've created a really rough Batch and Query SQL queries with basic understanding but could you review it?
This does not make sense, as long as you don't exactly define what your expected result is, i.e. which and how many tracks should be added to a playlist?

 

Re: [fb2k v2] Random Pools (foo_random_pools)

Reply #386
This does not make sense, as long as you don't exactly define what your expected result is, i.e. which and how many tracks should be added to a playlist?

Okay yeah, after playing through there seems to be a lot more involved in this than simply creating temporary tables. Honestly doubting if it's worth the trouble...

I'm trying to add only two tracks to my playlist and set auto-add to add more tracks if the tracks in the playlist are less or equal to one.

Re: [fb2k v2] Random Pools (foo_random_pools)

Reply #387
I'm trying to add only two tracks to my playlist and set auto-add to add more tracks if the tracks in the playlist are less or equal to one.
In this case you can use what I proposed, just set the LIMIT to 2. Of course, you will always have only 2 tracks which will come from different albums.

If you add additional tracks to the playlist with auto-add also only the new 2 tracks will not come from the same album. It's absolutely impossible to add only tracks from albums, which were not added before. Two selections from a pool are completely unrelated.

If you for example increase the LIMIT from 2 to 50 you will have at least 50 subsequent tracks from 50 different albums before another track is taken from one of these albums.

Re: [fb2k v2] Random Pools (foo_random_pools)

Reply #388
In this case you can use what I proposed, just set the LIMIT to 2. Of course, you will always have only 2 tracks which will come from different albums.

I assume the proposed one is this? I've tried this query but couldn't get [%jsp3_loved] WHERE condition to work, I forgot to clarify this earlier but [%jsp3_loved] means tracks which I've favourited... It's a bit weird but the SQL query correctly selects the rows which include my favourite tag and discards the rest.


WITH
Code: [Select]
CTE AS (
  SELECT DISTINCT
         first_value(path) OVER w_album path,
         first_value(subsong) OVER w_album subsong
  FROM MediaLibrary
  WINDOW w_album AS (PARTITION BY album ORDER BY random())
)

ORDER BY
Code: [Select]
random()

LIMIT
Code: [Select]
20

Re: [fb2k v2] Random Pools (foo_random_pools)

Reply #389
Hello again fbuser, I've been noticing this issue slowly creep up randomly at times and for no reason, after some point of playback with Random Pools it no longer adds any new tracks to the playlist... Running my SQL query on the SQLite console still retrieves results for the next tracks but the Console Log only shows this indicating no issue when I click on File>Random Pool>Pool name.

It seems to resolve only when I fully restart foobar2000. I have a gut feeling it might be due to active playlists. When I run a Random Pool for another Playlist and I'm on another active playlist then return nothing's been added to the playlist specified in Random Pool.

I've attached some images to look into... note that I haven't changed any settings here from my previous posts.

Re: [fb2k v2] Random Pools (foo_random_pools)

Reply #390
Hello again fbuser, I've been noticing this issue slowly creep up randomly at times and for no reason, after some point of playback with Random Pools it no longer adds any new tracks to the playlist...
This should happen only, if there are no tracks, which can be added. How many possible tracks do yo have for each of the pools (just run the query without the limit in the SQLite console to determine this).

Running my SQL query on the SQLite console still retrieves results for the next tracks but the Console Log only shows this indicating no issue when I click on File>Random Pool>Pool name.
There is a quite small chance that the query runs in the SQLite console again because the 5 hour limit is meanwile not filtering anymore, while it was the case during the execution of the pool's query for the "least recently played" pool but not for the other. Anyway, please provide me also the complete poolset settings and the settings of the second pool (lowest playcount).


Re: [fb2k v2] Random Pools (foo_random_pools)

Reply #391
This should happen only, if there are no tracks, which can be added. How many possible tracks do yo have for each of the pools (just run the query without the limit in the SQLite console to determine this).

There are plenty of tracks when I remove the LIMIT query, of course, the rows shrink once a track has been recently played but around 100+ tracks as an estimate.

There is a quite small chance that the query runs in the SQLite console again because the 5 hour limit is meanwile not filtering anymore, while it was the case during the execution of the pool's query for the "least recently played" pool but not for the other. Anyway, please provide me also the complete poolset settings and the settings of the second pool (lowest playcount).

Code: [Select]
Number of different randomly chosen pools to add: 2

Code: [Select]
-Autoadd-
Add, when the playlist has X items or less: 1
Add, when the playlist duration is X minutes or less: 0
Add, when the playback positions is X items or less from the end: 1
Pool set title SQL filter: [%jsp3_loved%]

Code: [Select]
-Pools-

Name: Least Recently Played
Group: %album%
Determine probability for picking groups based on group size: Untick
Number of groups to add: 1
Number of tracks to add: 0
Number of minutes to add: 0
Number of MBs to add: 0
Limit after sorting: Untick
Sort: Empty
Sort / limit each group: Empty
Source / Filter
Type: SQL (requires foo_sqlite)
Query
WITH: CTE AS(
 SELECT path, subsong, [%jsp3_loved%], COALESCE(NULLIF([%jsp3_last_played%], ''), '1970-01-01 00:00:00') AS [%jsp3_last_played%]
 FROM MediaLibrary
)

SELECT path, subsong FROM: CTE

WHERE: [%jsp3_last_played%] < datetime('now', '-300 minutes')

ORDER BY: [%jsp3_last_played%]

LIMIT: 2

Filter with the content of the target playlist: Tick
Filter with the content of the target playlist using a title format expression: Empty
Priority: 1

Code: [Select]
Name: Lowest Play Count
Group: %album%
Determine probability for picking groups based on group size: Untick
Number of groups to add: 1
Number of tracks to add: 0
Number of minutes to add: 0
Number of MBs to add: 0
Limit after sorting: Untick
Sort: Empty
Sort / limit each group: Empty
Source / Filter
Type: SQL (requires foo_sqlite)
Query
WITH: CTE AS(
 SELECT path, subsong, [%jsp3_loved%], COALESCE(NULLIF([%jsp3_playcount%], ''), 0) AS [%jsp3_playcount%], COALESCE(NULLIF([%jsp3_last_played%], ''), '1970-01-01 00:00:00') AS [%jsp3_last_played%]
 FROM MediaLibrary
)

SELECT path, subsong FROM: CTE

WHERE: [%jsp3_last_played%] < datetime('now', '-300 minutes')

ORDER BY: [%jsp3_playcount%]

LIMIT: 2

Filter with the content of the target playlist: Tick
Filter with the content of the target playlist using a title format expression: Empty
Priority: 1

Could it be Filter with the content of the target playlist? Since both of the pools have the same WHERE conditions but only the ordering is different.

Also with these two pools defined what I'm trying to achieve is to alternative between the Least Recently Played and Lowest Play Count when I have only a single track or less in my playlist.

Re: [fb2k v2] Random Pools (foo_random_pools)

Reply #392
Hello again, I guess I've resolved this issue by changing my Group to %track% and that seemed to keep my playlist always populated without running out of tracks.

Also with how I defined the probability for my pool does this achieve alternating between two pools by giving them a 1/2 chance of it being selected after one of these conditions is met?
Code: [Select]
Add, when the playlist has X items or less: 1
Add, when the playback positions is X items or less from the end: 1

Re: [fb2k v2] Random Pools (foo_random_pools)

Reply #393
Hello again, I guess I've resolved this issue by changing my Group to %track% and that seemed to keep my playlist always populated without running out of tracks.
This should not really make a difference, but as long as it works for you it's fine.

Also with how I defined the probability for my pool does this achieve alternating between two pools by giving them a 1/2 chance of it being selected after one of these conditions is met?
The probability for a pool is determined by a pools' priority related to the sum of the priorities for all pools. As you set the priority for both pools to 1 it's already what you want.

Re: [fb2k v2] Random Pools (foo_random_pools)

Reply #394
This should not really make a difference, but as long as it works for you it's fine.
Yeah I'm even more confused now on how changing the title format fixed it.

The probability for a pool is determined by a pools' priority related to the sum of the priorities for all pools. As you set the priority for both pools to 1 it's already what you want.
Thanks for confirming.

Re: [fb2k v2] Random Pools (foo_random_pools)

Reply #395
Hello again fbuser, I guess I'm ready to take up the challenge of preventing songs from the same album...

Before I start, the algorithms mentioned above are taken from a radio station. I was lucky enough to get in touch with the creator and he provided me with some sample SQL queries (as his radio station again uses SQL databases) to help me out.

When I implemented these queries it almost matched with how the Radio station selects songs but an issue crept up where when selecting songs to play from MediaLibrary, the SQL queries would not consider songs that had already been queued, and so I would experience two songs from the same album near one another (usually separated by one other song from another album).

The creator implemented a change into the database to maintain variety when listening and one such change was he made a  custom call to include queued songs when considering what to play next (Perhaps in our use case a custom table which contains the queued songs in Batch part?).

The custom call, allows the creator to look into circles (or album artists) that have played recently since the concept of circles (music groups, bands, etc.) didn’t exist in the program before this.

As a result, the system will attempt to space out songs from the same circle to further increase variety.

Now I know the queries I made will not space out songs from the same circle due to how it's made but would it be possible for you to assist me or know any idea as to how to achieve this to prevent songs from the same album/album artist appearing in a very short period? (If we prevent album artists this ultimately means preventing them from albums anyway hence the creator took this approach)

Re: [fb2k v2] Random Pools (foo_random_pools)

Reply #396
Hello fbuser, disregard the previous post as I've exhausted every option but it seems to be harder to achieve it now, Perhaps I'll try tweaking other values within the Random Pool and change SQL queries to get what I want. Before that, I have a small doubt...

Yes, this is basically correct. There is an easy but only manual solution: Assign to all relevant pools, which you have defined, the same keyboard shortcut. Another more complex solution, which adds more than one pool automatically, could be to utilize function on_playlist_items_removed(playlist, new_count) {} within foo_uie_wsh_panel_mod together with "Remove played tracks" from foo_playlist_attributes.


Is this possible with JScript Panel3? I've asked in the JScript discussion page which is similar to the answer you provided in your old post.
https://hydrogenaud.io/index.php/topic,110516.new.html#info_1036207

I want my playlist to be a mirror's copy of the SQL rows which have been retrieved and it only seems to be possible when assigning a keyboard shortcut which adds in All the pools. Auto-add doesn't seem to work well here and it looks like the only logical approach will be to clear the playlist after each track has been played back.

EDIT: Okay setting Auto-add to 1 and just clearing the playlist is another way of approaching an exact mirror's copy of the SQL rows selected. I have to code up some logic with the JScript Panel. Thanks.


Re: [fb2k v2] Random Pools (foo_random_pools)

Reply #398
I wanted to know if it's possible to combine the query you suggested to prevent tracks from the same album playing...

@fbuser continuing from here I guess I'm a bit keener now into implementing one I want to force my MediaLibrary to last_played and then space out music tracks with the same circle (or album artist). Regarding the spacing out is it possible to use the LAG function or any of that sort? Suppose there are 2 rows and both of them have the same circle next to each other would it be possible to space them in a way so that they are maybe 10 minutes apart? or X minutes apart from each other?

Code: [Select]
WITH CTE AS (
    SELECT
        path,
        subsong,
        codec,
        [album artist],
        COALESCE(NULLIF([%jsp3_last_played%], ''), '1970-01-01 00:00:00') AS [%jsp3_last_played%],
        ROW_NUMBER() OVER (PARTITION BY [album artist] ORDER BY COALESCE(NULLIF([%jsp3_last_played%], ''), '1970-01-01 00:00:00') DESC) AS row_num
    FROM MediaLibrary
)
SELECT path,
       subsong
FROM (
    SELECT
        CTE.*,
        LAG([album artist]) OVER (ORDER BY COALESCE(NULLIF([%jsp3_last_played%], ''), '1970-01-01 00:00:00') DESC) AS prev_artist
    FROM CTE
) AS T
WHERE (codec <> 'TAGS') AND (([%jsp3_last_played%] < datetime('now', '-300 minutes', 'localtime'))
    AND (
        row_num = 1 OR
        COALESCE(NULLIF([%jsp3_last_played%], ''), '1970-01-01 00:00:00') < datetime('now', '-10 minutes', 'localtime')
        AND (prev_artist IS NULL OR prev_artist <> [album artist])
    ))
ORDER BY CONCAT([%jsp3_last_played%], [album artist])

I've been scrolling through SQL functions and checking other forums but it seems harder to implement it... The code I've provided above is still incomplete...

Before this, if I only wanted to retrieve the Least Recently Played this query pretty much did the job.

Code: [Select]
WITH CTE AS(
 SELECT path, subsong, codec, [%jsp3_loved%], COALESCE(NULLIF([%jsp3_last_played%], ''), '1970-01-01 00:00:00') AS [%jsp3_last_played%]
 FROM MediaLibrary
)

SELECT path, subsong FROM CTE
WHERE [%jsp3_last_played%] < datetime('now', '-300 minutes', 'localtime')
ORDER BY [%jsp3_last_played%]

But I want to know if it's possible to space out album artists/circles.

Re: [fb2k v2] Random Pools (foo_random_pools)

Reply #399
But I want to know if it's possible to space out album artists/circles.
Maybe, but you probably go better in general with creating temporary tables with subresults in the batch part as it is becoming quite complex.