Extend Category Rotation Time and Prevent Pattern

[thaJack]

I wanted to share with you guys a query that I've written to select a song for a specific category. It is a combination of two queries, so there are some inconsistencies throughout in how it's written, but it seems to work nonetheless. But, could use some cleanup and removal of redundant code.

Anyway, the purpose is twofold:

• Prevent a pattern from forming. If I use "least recently played", eventually, with the exception of a few differences caused by rotation rules, a pattern will form and it will play the category over and over with songs in mostly the same order.
• Make a category last longer before it runs out of songs.

Here's the query, and I'll try to explain it below:

Code Select Expand


-- FLEX C 150 WIITH RECYCLE

IF HOUR(CURTIME()) BETWEEN 00 AND 05 THEN

-- RECYCLE
-- Category: C
-- This query will select a song that was played yesterday between
-- 7am and 6pm the previous day
-- if the current hour is between 00 and 05

SELECT e.*
FROM (
   SELECT sn.*
   FROM songs sn
   LEFT JOIN subcategory sc
   ON sc.ID = sn.id_subcat
   LEFT JOIN queuelist ql
   ON ql.songID = sn.ID
   WHERE sn.song_type = 0
   AND sc.name = 'C'
   AND sn.enabled = 1
   AND ql.songID IS NULL
   AND sn.date_played >= (SELECT CONCAT(DATE_SUB(CURDATE(), INTERVAL 1 DAY), ' 07:00:00.000'))
   AND sn.date_played <= (SELECT CONCAT(DATE_SUB(CURDATE(), INTERVAL 1 DAY), ' 17:59:59.000'))
   AND $ForceRepeatRules$
   ORDER BY sn.date_played
) e
ORDER BY RAND()
LIMIT 1;

ELSE

-- C - FLEX 150 --

SELECT *
FROM (
SELECT songs.*, queuelist.songID
FROM songs
LEFT JOIN queuelist ON queuelist.songID = songs.ID
WHERE songs.id_subcat = 30 -- C
AND enabled = 1
AND queuelist.songID IS NULL
AND $ForceRepeatRules$
ORDER BY date_played ASC
LIMIT 150) e
ORDER BY RAND()
LIMIT 1;

END IF;


The IF statement at the top checks to see if the current hour is between 00 (midnight) and 05 (5am). If so, then the query in that code block will run. While still enforcing repeat rules, and checking to be sure the track isn't already in the queuelist, it will randomly select a track that was played between 7am and 5:59pm the previous day. If you're listening during those hours, it's unlikely you'll be listening during the overnight hours, so it will reuse songs, extending the life of the category before it begins to repeat tracks again.

If it's not between midnight and 5:59am, then the second code block will run (after ELSE). This query will find the 150 least recently played songs in the category, while still respecting the repeat rules and making sure the track isn't already in the queuelist. It will then randomly select one of them. In effect, this starts to "shuffle" the category a bit and prevent it from repeating in the mostly same order over and over. You'd want to change the 150 to a number that works well for you.

For this particular category, I call it this way most of the time. I do have at least one "least recently played" selection for the category per hour, which should ensure that every track eventually gets played, and doesn't fall victim to the randomness and never get picked.

I hope this helps someone. Again, the query could be cleaner for sure.

[thaJack]

Well, it seems this query doesn't return anything during the 00-05 hours. Trying to figure out what's wrong with it.

[thaJack]

I think I fixed it by replacing aliases (e.g., "sn", "sc", "ql") with table names in the first block of code. The part that forces the rotation rules used table names and I think it was getting confused.

[thaJack]

It seems that my fix worked. Here is a list of the trackIDs that played overnight (between midnight and 6am) along with any other time they played during the previous day. Note that not every song will appear twice because there are still some requests for the least recently played track.

Code Select Expand


75 2021-12-14 14:24:53.000
75 2021-12-15 04:42:33.000
296 2021-12-14 07:11:00.000
296 2021-12-15 03:24:17.000
585 2021-12-14 12:15:42.000
585 2021-12-15 01:27:26.000
634 2021-12-14 14:53:06.000
634 2021-12-15 05:43:38.000
666 2021-12-14 15:45:29.000
666 2021-12-15 00:24:24.000
936 2021-12-14 12:53:13.000
936 2021-12-15 03:07:08.000
987 2021-12-14 14:07:59.000
987 2021-12-15 00:07:49.000
1059 2021-12-14 11:59:07.000
1059 2021-12-15 03:44:56.000
1323 2021-12-15 04:51:51.000
1377 2021-12-15 01:58:43.000
1779 2021-12-14 16:54:40.000
1779 2021-12-15 04:24:30.000
1803 2021-12-14 13:54:34.000
1803 2021-12-15 02:11:03.000
2062 2021-12-14 17:29:12.000
2062 2021-12-15 01:46:57.000
2261 2021-12-14 10:28:37.000
2261 2021-12-15 05:23:00.000
2371 2021-12-14 16:28:09.000
2371 2021-12-15 02:27:51.000
2471 2021-12-14 15:28:45.000
2471 2021-12-15 04:07:16.000
2604 2021-12-15 02:57:42.000
2649 2021-12-15 03:54:59.000
3340 2021-12-14 08:11:30.000
3340 2021-12-15 02:48:56.000
3490 2021-12-14 16:42:56.000
3490 2021-12-15 00:44:33.000
3575 2021-12-15 05:53:57.000
3603 2021-12-14 07:48:16.000
3603 2021-12-15 05:06:06.000
3680 2021-12-14 09:08:04.000
3680 2021-12-15 01:09:04.000
4282 2021-12-15 00:55:11.000


If I did all least recently played, this category would take about twenty-five days to turn over. This will make it last a little bit longer.

[packzap]

Thanks for these ideas Jack, to help break up common music "patterns" that seem to occur over a week or more.