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 175313 times) previous topic - next topic
0 Members and 1 Guest are viewing this topic.

Re: [fb2k v2] Random Pools (foo_random_pools)

Reply #400
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

Code: [Select]
-- 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;

Re: [fb2k v2] Random Pools (foo_random_pools)

Reply #401
Is there any chance, that the random pool could get a retry mechanism?
Background: Some Pools in my setup are influenced by the songs actually being played - like I try to emphasize songs, that haven't been played for a long time. But sooner or later, the pools with the songs not being played for a long time are empty.
What happens, when the random pool plugin tries to keep the playlist topped up (by adding songs when there is less than x minutes or songs remaining), it chooses one of the pools based on probability, and picks an empty pool. No song gets added. A retry mechanism, that would keep going until one song gets added would help with that.
Alternatively, the Random Pool plugin could check if a pool actually has members and if not, automatically eliminate this Pool from the chosen Pool collection and only choose among the rest.

Actually... I take all that back. Somethign different seems to be going on, not sure yet what. I'll keep investigating.

 

Re: [fb2k v2] Random Pools (foo_random_pools)

Reply #402
And actually it turns out, I had some broken grouping expressions within my pools. Setting up everything correctly, it seems to work fine.