Maybe, but you probably go better in general with creating temporary tables with subresults in the batch part as it is becoming quite complex.
It seems alot harder after spending weeks, Currently I'm just sucking it up and shuffling based on Lowest Play Count and Least Recently Played which was already figured but coming to the point.
It took awhile to come up with this SQL query but the gist of it is that if it's possible to know what's being played in playlist and comparing it to media library that should help in spacing out how far apart the same circle should occur every time when the Random Pool is called (currently shuffles random pools after each track).
I feel like maybe another temp table is needed just because we are going to reshuffle order by spacing circles alone
-- Create temporary table tmpMediaLibraryFLPC
DROP TABLE IF EXISTS tmpMediaLibraryFLPC;
CREATE TEMPORARY TABLE tmpMediaLibraryFLPC AS
SELECT
path,
subsong,
codec,
[album artist] AS media_library_circle,
[%jsp3_loved%],
CAST(COALESCE(NULLIF([%jsp3_playcount%], ''), 0) AS INTEGER) AS [%jsp3_playcount%],
COALESCE(DATETIME(NULLIF([%jsp3_last_played%], '')), '1970-01-01 00:00:00') AS [%jsp3_last_played%],
ROW_NUMBER() OVER (ORDER BY [%jsp3_playcount%]) AS custom_unique_identifier_media_library -- Create a custom unique identifier for MediaLibrary
FROM MediaLibrary
WHERE (codec <> 'TAGS' AND [%jsp3_loved%]) AND [%jsp3_last_played%] < datetime('now', '-300 minutes', 'localtime')
ORDER BY [%jsp3_playcount%];
-- Create temporary table tmpPlaylistFLPC
DROP TABLE IF EXISTS tmpPlaylistFLPC;
CREATE TEMPORARY TABLE tmpPlaylistFLPC AS
SELECT
[album artist] AS playlist_circle,
ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS custom_unique_identifier_playlist -- Create a custom unique identifier for Playlist
FROM Playlist;
-- Common Table Expression (CTE)
WITH CTE AS (
SELECT
m.path,
m.subsong,
m.media_library_circle,
p.playlist_circle
FROM tmpMediaLibraryFLPC m
JOIN tmpPlaylistFLPC p ON m.custom_unique_identifier_media_library = p.custom_unique_identifier_playlist
)
-- Select path, subsong, media_library_circle, and playlist_circle from the CTE
SELECT path, subsong, media_library_circle, playlist_circle
FROM CTE
LIMIT 10;