One more thing to tackle, how to not play recent songs

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.

Secondly, is it possible to prevent songs from the same album from showing up?

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())
)

random()

20

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?

WINDOW w_album AS (PARTITION BY album ORDER BY random())

first_value(path) OVER w_album path,
first_value(subsong) OVER w_album subsong

random()

20

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

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?

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.

COALESCE(NULLIF([%jsp3_last_played%], ''), '1970-01-01 00:00:00') < datetime('now', '-300 minutes')
AND [%jsp3_loved%]

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.

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).

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?

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%

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();

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

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?

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.

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

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.

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 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).

Number of different randomly chosen pools to add: 2

-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%]

-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

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

Add, when the playlist has X items or less: 1
Add, when the playback positions is X items or less from the end: 1

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?

This should not really make a difference, but as long as it works for you it's fine.

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.

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 wanted to know if it's possible to combine the query you suggested to prevent tracks from the same album playing...

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])

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.