Shuffle categories (assistance with a SQL query)

[thaJack]

I use "Least Recently Played" for all songs. I don't want to use random ever. However, as most of you know, the categories end up starting to become predictable because the songs will run in order (barring title/artist/etc separation rules).

I'd like to periodically reset the last played date/time for songs at the bottom of my category so it will somewhat randomize them again. I'm having trouble with a SQL query to do it. Maybe someone here can help.

Get count of songs in the category and then figure out what 20% (or whatever percent, depending on the category) is. So, if the category has 1,000 songs in it, then it would come up with 200 as my number.

Then, select the songs, ordering by date/time played ascending (so the oldest ones come first), then reset the date/time played on the first 200 songs.

Does this make sense?

[mdj]

I would suggest against resetting any data in the database, and I think you can achieve the desired result without having to do it, with the exception of using dynamic percentage.

SELECT *
FROM (
   SELECT sn.ID
   FROM subcategory sc
   LEFT JOIN songs sn
   ON sn.id_subcat = sc.ID
   WHERE sc.name = 'DEC80s' -- < your category name here
   ORDER BY sn.date_played ASC
   LIMIT 200 -- < the number that you want to limit it to
) e
ORDER BY RAND()
LIMIT 1

This will get the last 200 least recently played and randomly choose 1 of them.

[thaJack]

MDJ,

That's a great idea. What can I add to the query to ensure that all appropriate separation rules are also followed?

[thaJack]

Also, I will want to join the queue so that I ensure the rules consider songs that are already there.

Thanks in advance!

[mdj]

SELECT *
FROM (
   SELECT sn.*
   FROM subcategory sc
   LEFT JOIN songs sn
   ON sn.id_subcat = sc.ID
   LEFT JOIN queuelist ql
   ON ql.songID = sn.ID
   WHERE sc.name = 'DEC80s' -- < your category name here
   AND ql.ID IS NULL -- < required to exclude those already in the queuelist
   ORDER BY sn.date_played ASC
   LIMIT 200 -- < the number that you want to limit it to
) e
$ArtistRepeatInterval$
$TrackRepeatInterval$
$ForceRepeatRules$
ORDER BY RAND()
LIMIT 10

Because this is a query in a query, and all the repeat rules do is add a where clause to the query, I'm not sure which query it will add these to, so I would suggest increasing the limit to 10.
The preferred option would be to move the repeat rules into the query manually (take it out of the hands of rDJ).
Regards
Mike.

[Lotus]

I was using least played on all of the track rotations that I built and started to notice a pattern. I now do this and like it. I have rules for separation by artist/album/song.

say this is a 15 song hour

making this up....

80s Least Recently Played
80s Random
90s Least Recently Played
2020s
90s Random
80s Least Times Played
80s Random
90s Least Times Played
2010s
90s Random
80s Least Recently Played
80s Least Times Played
90s Least Recently Played
2000s
90s Least Times Played

By mixing it up like that I have not noticed any type of pattern since.

[thaJack]

Lotus -

My issue with random is that you could end up with a song all the way at the top of the stack just below the threshold for the separation rules.

I feel like if, instead of Least Recently Played, you pick from a song somewhere near the bottom of the stack, that otherwise meets the requirements, then it will introduce some level of randomness to it (avoiding the pattern) without having to go all-in random.

For example, if I've got 200 songs in the stack, instead of taking the single song that is least recently played (which will always end up forming a pattern except in cases where the pattern is broken by separation rules), I could say to take any song from the bottom 20% of the stack. It's been long enough since any of those played, so any of them should be fine, and it will avoid the pattern.

[Lotus]

True. I do like that. It got me thinking (and I will make a different post). Curious what separation rules and reasoning people use. off to make that post now.

[thaJack]

Here's the query I've decided to try:

Code Select Expand

-- B - FLEX 40 --

SELECT *
FROM (
SELECT *
FROM songs
WHERE songs.id_subcat = (
SELECT ID
FROM subcategory
WHERE parentID = (
SELECT ID
FROM category
WHERE name = 'Music'
)
AND name = 'B - Medium/Heavy'
)
AND enabled = 1
AND $ForceRepeatRules$
ORDER BY date_played ASC
LIMIT 40) e
ORDER BY RAND()
LIMIT 1

It should randomly select one song from the forty least-recently played songs that meet the separation rules.

I'm using this for two different categories. If I use the SQL query exclusively, the randomization could potentially keep some songs from never being picked, so I am using the SQL query half the time, and Least Recently Played half the time. That should ensure that all songs eventually get picked.

[thaJack]

Can anyone provide advice on how to get the query in the previous message to consider tracks already in the queue?

[mdj]

Can anyone provide advice on how to get the query in the previous message to consider tracks already in the queue?

Left join the queue list table by song ID, then exclude any that are populated by only selecting those that are NULL.

LEFT JOIN queuelist ql
ON ql.songID = sn.ID

and in the WHERE include

ql.ID IS NULL


I've also condensed your query using a join instead of a nested select, and there is no need to sub query the music items, as we know they will be type 0.

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 = 'B - Medium/Heavy'
   AND sn.enabled = 1
   AND ql.ID IS NULL
   -- repeat rules here
   ORDER BY sn.date_played
   LIMIT 40
) e
ORDER BY RAND()
LIMIT 1

I've also been thinking about your original request about doing the bottom 20% of the least recently played, and I think I have come up with a solution.

So, if the category has 1,000 songs in it, then it would come up with 200 as my number.

Although, this is the bottom 20% according to date, not the last 20% according to the count in that sub cat, that's another query.

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 = 'B - Medium/Heavy'
   AND sn.enabled = 1
   AND ql.ID IS NULL
   AND UNIX_TIMESTAMP(date_played) <
      (SELECT
         FLOOR(
            (MAX(UNIX_TIMESTAMP(sn2.date_played)) -
            MIN(UNIX_TIMESTAMP(sn2.date_played)))
            * (20 / 100) -- change the 20 to the percentage you want, or if you are confident just change this to * 0.2
            + MIN(UNIX_TIMESTAMP(sn2.date_played))
         )
         FROM songs sn2
         LEFT JOIN subcategory sc2
         ON sc2.ID = sn2.id_subcat
         WHERE sn2.song_type = 1
         AND sc2.name = "B - Medium/Heavy'
         AND sn2.enabled = 1
      )
   -- repeat rules here
   ORDER BY RAND()
   -- ORDER BY sn.date_played -- no need to order it anymore
   -- LIMIT 40 -- no need for this limit anymore
) e
LIMIT 1


I'm typing this up on a PC that I dont have access to rDJ on, so please excuse any spelling errors (of tables or field names), you may need to do some troubleshooting if this does not work.
Mike

[thaJack]

Thanks, Mike. I'll take a look at that. Appreciate it very much.

[thaJack]

Mike,

I tried the query. It didn't error, but also didn't return any results.

[thaJack]

Hey Mike,

I still haven't implemented the last query (percentage), but the one I was using now looks at the queue and won't add a track that is already in the queue. However, it doesn't seem to consider artists that are in the queue, as I had two songs by the same artist play in a row this morning (on the first one, the artist was a featured artist, and on the following song, they were the only artist).

Is there a way to get the artist separation to consider the artists in the queue? I've seen some posts which reference columns that don't exists in the queuelist table.

[mdj]

Mike,

I tried the query. It didn't error, but also didn't return any results.

Yup, there are two mistakes in there, double quotes in the date_played sub query, and also song_type is 1 in that query, it should be 0.

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 = 'B - Medium/Heavy'
   AND sn.enabled = 1
   AND ql.ID IS NULL
   AND UNIX_TIMESTAMP(date_played) <
      (SELECT
         FLOOR(
            (MAX(UNIX_TIMESTAMP(sn2.date_played)) -
            MIN(UNIX_TIMESTAMP(sn2.date_played)))
            * (20 / 100) -- change the 20 to the percentage you want, or if you are confident just change this to * 0.2
            + MIN(UNIX_TIMESTAMP(sn2.date_played))
         )
         FROM songs sn2
         LEFT JOIN subcategory sc2
         ON sc2.ID = sn2.id_subcat
         WHERE sn2.song_type = 0
         AND sc2.name = 'B - Medium/Heavy'
         AND sn2.enabled = 1
      )
   -- repeat rules here
   ORDER BY RAND()
   -- ORDER BY sn.date_played -- no need to order it anymore
   -- LIMIT 40 -- no need for this limit anymore
) e
LIMIT 1