Author Topic: Rotation problem with tracks still in playlist  (Read 202 times)

chrismadog

  • Sr. Member

  • Offline
  • ****
  • 250
  • Personal Text
    Be part of the solution, not part of the problem !
    • Rose City FM
Rotation problem with tracks still in playlist
« on: May 15, 2019, 03:09:34 AM »
Hi Marius,

Using RadioDJ v2.0.1.5 on laptop  - Dual core P6200 @ 2.13GHz, 16GB RAM, tons disk space, minimal other programs running on Windows 10.

I made the following SQL rotation with the intent of having a Queen Special on a 3 hr program without too many in the playlist at one time contributing to program lag.

SELECT `ID`, `artist`,`title` FROM `songs`
WHERE title LIKE '%*%'
AND NOT title LIKE '%[LANGUAGE]%'
AND artist LIKE 'Queen%'
AND enabled = '1'
AND year BETWEEN '1970' AND '1979'
AND $ForceRepeatRules$
ORDER  BY RAND ()
LIMIT 20

The problem is that it plays some tracks of the playlist and then adds new tracks - but the tracks it added also contain songs still remaining in the playlist which will result in those songs being repeated.

Is there a setting or SQL command that will prevent that ?

Regards,
Chris
Presenter - 4SDB - Rose City FM 89.3 - Warwick, QLD Australia.
Community Radio - Bringing you the best of local radio.
http://rosecityfm.org.au

stevewa

  • Sr. Member

  • Offline
  • ****
  • 444
Re: Rotation problem with tracks still in playlist
« Reply #1 on: May 15, 2019, 08:16:26 PM »
sure, add additional constraints to your SQL query, using DateDiff() to make sure date/time lastplayed is greater than 60 minutes...

Calypso

  • Sr. Member

  • Online
  • ****
  • 334
Re: Rotation problem with tracks still in playlist
« Reply #2 on: May 16, 2019, 11:05:31 AM »
sure, add additional constraints to your SQL query, using DateDiff() to make sure date/time lastplayed is greater than 60 minutes...

$ForceRepeatRules$ should already take care of this, and adding more limiters that in fact try to achieve the same will only make the query slower.

Also, TS mentions that the problem occurs with automatically adding tracks that are still in the queue to be played. With a date diff you only look at the time difference of songs that have been played. You should however look into the songs that are already scheduled to be played (and are in the queuelist table), and $ForceRepeatRules$ takes care of this.

@TS: What database do you use? MySQL/MariaDB and version? It could be that you're using MySQL 8, there are problems with some new implementations of it concerning things like RegExp. That could explain the problem.

mdj

  • New User

  • Offline
  • *
  • 9
Re: Rotation problem with tracks still in playlist
« Reply #3 on: May 17, 2019, 01:36:59 PM »
To stop this from occuring, I use a LEFT JOIN queuelist. Doing this twice, once for artist and once for title, then making sure that the resulting query is NULL for both the artist and title from queuelist.

Code: [Select]
SELECT ID
FROM (
SELECT so1.*, RAND() AS rand_order
FROM songs so1
LEFT JOIN subcategory sc1
ON sc1.ID = so1.id_subcat
LEFT JOIN queuelist q1
ON q1.songID = so1.ID
LEFT JOIN queuelist q2
ON q2.artist = so1.artist
WHERE sc1.NAME = 'DEC60s'
AND weight >=50
AND artist_played <= NOW() - INTERVAL 4 HOUR
AND date_played <= NOW() - INTERVAL 12 HOUR
AND count_played <= (
SELECT ROUND(AVG(sox.count_played))
FROM songs sox
LEFT JOIN subcategory scx
ON scx.ID = sox.id_subcat
WHERE sox.song_type = 0
AND scx.name = 'DEC60s'
)
AND q1.ID IS NULL
AND q2.ID IS NULL
ORDER BY rand_order ASC
) e
LIMIT 1

stevewa

  • Sr. Member

  • Offline
  • ****
  • 444
Re: Rotation problem with tracks still in playlist
« Reply #4 on: May 19, 2019, 04:07:18 PM »
nice example!

chrismadog

  • Sr. Member

  • Offline
  • ****
  • 250
  • Personal Text
    Be part of the solution, not part of the problem !
    • Rose City FM
Re: Rotation problem with tracks still in playlist
« Reply #5 on: May 20, 2019, 01:15:35 AM »
Nice example of the sorts of things that can be done with SQL queries but way too complicated for me. :)

I think I will tolerate the program lag with 45+ songs in it and make the limit 60 to make sure I get them all first time around and way more than I need for the show.
That means I can delete tracks that don't fit the flow or are played way too many times by other presenters.

I also do the same sort of thing with other artists but in particular, specific years or decades of music.

So, I need to make sure there are enough tracks from go-get to get me through the whole show. Sometimes I can do a double show - 6 hrs so that can mean a lot of tracks and a lot of program lag (especially for 50s & 60's music).

Such is life.
Presenter - 4SDB - Rose City FM 89.3 - Warwick, QLD Australia.
Community Radio - Bringing you the best of local radio.
http://rosecityfm.org.au