Play songs at specific days/ query explanation

[ruvo80]

Hi guys,
First of all.. I typed this message in wordpad and I know it is very long. But please take your time to read. I promise it is easy to read even if you don't know anything about queries, I am sure at the end you will understand lots more!
Maybe I do have to tell you that I wasn't familiar to any scriptwriting till 2 months ago so I am sure any person without lots of knowledge of it will understand at the end of the message.

and now the message:

The way we all workin this forum is stunning. Everyone is willing to help eachother where possible. Instead of the support for all expensive softwares, again radiodj proves that people make the product and support!
So now I want to help you guys. I found something I have to share with you all!

In november there was a treath about holidays.. Someone on this forum asked if it was possible to play songs for christmas only between 6 an 31 december.
Again the people in the forum worked togheter and found a great sollution in making sql queries.
I been using this and I loved it. But there were a few things I wanted to change about it. When u used that querie.. you had to make a few events and most of all.. songs were disabled and not able to played  in dates without the start and end date! (This is not bad for christmas songs, but when you do the same trick in summer songs.. it means you never can play a summer song outside start and end date easily)

Well I found a way now. you not need to have start and end dates on the tracks, neither you need any event to activate the songs or update the year to next year! it all runs itself and the songs won't be disabled. so if you like to play a christmas song on 1 may.. just add it and it plays

Here is the full query you need in the rotation (I will explain all pieces of it seperatly after the full query)


SELECT * FROM (
      #Christmas
      (SELECT `songs`.`ID`, `songs`.`artist`, `songs`.`title`, `songs`.`date_played`, `songs`.`artist_played`,`songs`.`id_subcat`, `songs`.`weight`, 1 AS `preference` FROM `songs`
      LEFT JOIN `queuelist` ON ( `songs`.`artist` = `queuelist`.`artist`)
      WHERE `songs`.`enabled` = 1
      AND `songs`.`song_type` = 0
      AND `songs`.`id_subcat` = 59
      AND `songs`.`id_genre` = 99
      AND (TIMESTAMPDIFF(MINUTE, `songs`.`date_played`, NOW()) > $TrackRepeatInterval$)
      AND (TIMESTAMPDIFF(MINUTE, `songs`.`artist_played`, NOW()) > $ArtistRepeatInterval$)
      AND (MONTH(CURDATE()) = 12)
      AND (DAY(CURDATE()) >= 6)
      AND (DAY(CURDATE()) <= 31)   
      AND  `queuelist`.`artist` IS NULL
      AND `weight`=75
      ORDER  BY RAND ()
      LIMIT 20)

   UNION
      #Summer
      (SELECT `songs`.`ID`, `songs`.`artist`, `songs`.`title`, `songs`.`date_played`, `songs`.`artist_played`,`songs`.`id_subcat`,  `songs`.`weight`, 2 AS `preference` FROM `songs`
      LEFT JOIN `queuelist` ON ( `songs`.`artist` = `queuelist`.`artist`)
      WHERE `songs`.`enabled` = 1
      AND `songs`.`song_type` = 0
      AND `songs`.`id_subcat` = 68
      AND `songs`.`id_genre` = 151
      AND (TIMESTAMPDIFF(MINUTE, `songs`.`date_played`, NOW()) > $TrackRepeatInterval$)
      AND (TIMESTAMPDIFF(MINUTE, `songs`.`artist_played`, NOW()) > $ArtistRepeatInterval$)
      AND (MONTH(CURDATE()) >= 6)
      AND (MONTH(CURDATE()) <=
      AND (DAY(CURDATE()) >= 1)
      AND (DAY(CURDATE()) <= 31)
      AND  `queuelist`.`artist` IS NULL
      AND `weight`=75
      ORDER  BY RAND ()
      LIMIT 20)

   UNION
      #90 POP
      (SELECT `songs`.`ID`, `songs`.`artist`, `songs`.`title`, `songs`.`date_played`, `songs`.`artist_played`,`songs`.`id_subcat`,  `songs`.`weight`, 3 AS `preference` FROM `songs`
      LEFT JOIN `queuelist` ON ( `songs`.`artist` = `queuelist`.`artist`)
      WHERE `songs`.`enabled` = 1
      AND `songs`.`song_type` = 0
      AND `songs`.`id_subcat` = 68
      AND `songs`.`id_genre` = 99
      AND (TIMESTAMPDIFF(MINUTE, `songs`.`date_played`, NOW()) > $TrackRepeatInterval$)
      AND (TIMESTAMPDIFF(MINUTE, `songs`.`artist_played`, NOW()) > $ArtistRepeatInterval$)
      AND  `queuelist`.`artist` IS NULL
      AND `weight`=75
      ORDER  BY RAND ()
      LIMIT 20)
   ORDER BY `date_played` asc
   LIMIT 60
)
temp
ORDER BY `preference`, `date_played` asc
LIMIT 1;



It looks pretty difficult but I can explain in an easy way!
Like you can see the word UNION is passing by several times.. Every text between 2 'Unions' is searching for a specific songtype.
If you look well you see 3 blockes:
#Christmas
#Summer
#90 POP

Every block is quite the same, and I want to explain you all the way it is build.
Let me open the first one ( CHRISTMAS) and explain the lines:

      #Christmas
      (SELECT `songs`.`ID`, `songs`.`artist`, `songs`.`title`, `songs`.`date_played`, `songs`.`artist_played`,`songs`.`id_subcat`, `songs`.`weight`, 1 AS `preference` FROM `songs`
      LEFT JOIN `queuelist` ON ( `songs`.`artist` = `queuelist`.`artist`)
      WHERE `songs`.`enabled` = 1
      AND `songs`.`song_type` = 0
      AND `songs`.`id_subcat` = 59
      AND `songs`.`id_genre` = 99
      AND (TIMESTAMPDIFF(MINUTE, `songs`.`date_played`, NOW()) > $TrackRepeatInterval$)
      AND (TIMESTAMPDIFF(MINUTE, `songs`.`artist_played`, NOW()) > $ArtistRepeatInterval$)
      AND (MONTH(CURDATE()) = 12)
      AND (DAY(CURDATE()) >= 6)
      AND (DAY(CURDATE()) <= 31)   
      AND  `queuelist`.`artist` IS NULL
      AND `weight`=75
      ORDER  BY RAND ()
      LIMIT 20)

That was the block. Now I will explain it line by line:

      #Christmas
THIS IS JUST A NAME TO KNOW WHAT SONG WILL BE SELECTED

      (SELECT `songs`.`ID`, `songs`.`artist`, `songs`.`title`, `songs`.`date_played`, `songs`.`artist_played`,`songs`.`id_subcat`, `songs`.`weight`, 1 AS `preference` FROM `songs`
      LEFT JOIN `queuelist` ON ( `songs`.`artist` = `queuelist`.`artist`)
THESE ARE ALL THE FIELDS RADIODJ HAS TO SEARCH FOR IN THE DATABASE
IT IS COMPLICATED TO EXPLAIN BUT THE MOST IMPORTANT IS "1 AS `preference`" BECAUSE IT WILL MAKE A SPECIFIC ORDER TO LOAD THE SONGS (AS YOU SEE EVERY SEARCH OF SONG HAS A DIFFERENT PRFERENCE NUMBER)

      WHERE `songs`.`enabled` = 1
      AND `songs`.`song_type` = 0
      AND `songs`.`id_subcat` = 59
      AND `songs`.`id_genre` = 99
NOW THE REAL SEARCH STARTS.
IT LOOKS FOR ENABLED SONGS
song_type 0 = MUSIC (1 FOR JINGLES AND SO ON
id_subcat = THE ID FOR SUBCATEGORIES (IN MY CASE 59 = CHRISTMAS)
id_genre = THE ID FOR GENRE (IN MY CASE 99 = POP)
(IF YOU DON'T KNOW YOUR ID'S, LOOK IN YOU DATABASE. YOU CAN FIND ALL ID'S AND NAMES THERE. MY TIP IS TO MAKE A LIST AND SAVE IT ON YOUR COMPUTER SOMEWHERE)

      AND (TIMESTAMPDIFF(MINUTE, `songs`.`date_played`, NOW()) > $TrackRepeatInterval$)
      AND (TIMESTAMPDIFF(MINUTE, `songs`.`artist_played`, NOW()) > $ArtistRepeatInterval$)
THIS IS ARTIST SEPERATION. IT IS USED LIKE YOU SET IN THE OPTIONS SCREEN OF RADIODJ

      AND (MONTH(CURDATE()) = 12)
      AND (DAY(CURDATE()) >= 6)
      AND (DAY(CURDATE()) <= 31)
WELL THIS IS THE BEST PART, IT ONLY LOADS THE SONGS IF THE CURRENT DATE IS BETWEEN 6-12 AND 31-12

      AND  `queuelist`.`artist` IS NULL
IT LOOKS IF THE SONGS IS ALREADY LOADED WITHOUT BEING PLAYED (SO YOU WON'T GET THE SONG TWICE IN THE ROTATION THAT IS LOADING)

      AND `weight`=75
NOW THE WEIGHT THING IS SOMTHING PERSONAL.. I USE 75 FOR HIGH ROTATION, 50 FOR MIDDLE ROTATION AND 25 FOR LOW ROTATION.
YOU ARE ABLE TO PLAN THE SONGS EVEN BETTER (DURING DAY I ONLY USE 75, IN EVENING 50 AND IN NIGHT ALSO 25)
IF YOU WILL USE THIS. MAKE SURE THAT IN SETTING YOU HAVE "ON PLAY, REDUCE BY PRIORITY" IS SET TO 0,0

      ORDER  BY RAND ()
      LIMIT 20)
SELECT 20 RANDOM SONGS.
I DON'T USE 1 BECAUSE LATER IN THE SCRIPT I WILL SELECT JUST 1!




this is all. But I know you just want 1 song to be added in the rotation not 20.
Well here is the explain. At the end of the whole querie you will find this text:

   ORDER BY `date_played` asc
   LIMIT 60
)
temp
ORDER BY `preference`, `date_played` asc
LIMIT 1;

Again I will cut this text in parts to explain:

   ORDER BY `date_played` asc
   LIMIT 60
I USED LIMIT 20 IN THE PREVIOUS PART. AS YOU SEE IN THE FULL QUERIE I SEARCH FOR 3 SONGTYPES (CHRISTMAS, SUMMER, 90 POP).
FOR EACH DONGTYPE I LET THE DATABASE SEARCH FOR 20 SONGS.
IN THE TEXT ABOVE, THE DATABASE PLACES ALL 60 SONGS IN ORDER DATE OF PLAYED

)
temp
ORDER BY `preference`, `date_played` asc
LIMIT 1;

THIS IS THE END OF THE QUERIE ALREADY.
IT ORDERS SONGS BY PREFERENCE (LIKE I SHOWED  BEFORE) AND DATE PLAYED ASWELL.
AND FINALLY IT LEAVES JUST 1 SONG TO ADD TO THE ROTATION!!!

As I said in the beginning, it is quite some text but I really hope it is usefull for you all.
Again, in this oppertunity you don't need to use any events, start and end dates of songs. it just loads the songs when the current date is right without having unnecesairly disabled songs in your database.
Hope it was usefull.

Ruud

[Marius]

WOW! Nice job Ruud! 

[ruvo80]

Thx Marius,

Am honered that you made my message  a sticky post. I hope it will help lot of people in understanding queries and the function of it!
I thought that not always a question was needed to help your radiofriends in here!

Ruud

[DJ Garybaldy]

Thx Marius,

Am honered that you made my message  a sticky post. I hope it will help lot of people in understanding queries and the function of it!
I thought that not always a question was needed to help your radiofriends in here!

Ruud

It was me who stickied it!  Just thought it would be more useful pinned.

[bulik4lg]

If you don´t mind, I add my five cents to it here.
At least Christmas songs can be solved also with a rotation and Date limit of each appropriate song (I set "Played after" 1st December and "Played until" 26th December).
I made rotations, for all 12 months of the year. All of them contain lines with Christmas jingle follwed with a Christmas song.
I use special item for christmas songs (like there is Music, Jingles, Sweepers etc., I created also Xmas to this list).
Each Christmas jingle and Christmas song has the date limit within it can be played (or better to say, it can be selected by a rotation) and the only thing which needs to be done once a year is to set the date limit for each item from Christmas jingles and Christmas songs.
No special query needed.
I doubt to say, the same thing can be done with summer songs as well.

Indeed there is plenty of situation where query is better solution. But I am almost sure those two (Christmas and Summer hits) can be solved in an easier way than sql query.

[Polar]

Old post, but just what I was looking for (I think ...). Where I believe UNION also can be used is in following situation:

When creating a format clock, categories Billboard #1 / Billboard top 30 are actually "Current", categories 2010 / 2000 need to be considered as one category "Recurrent", where as 1990 / 1980 / 1970 should be looked at as "Gold".

Super thanks for this !!!

[chrismadog]

Thank you ruvo80 !

The explanation was worth it's weight in gold to me. Particularly the 'Left Join' part and how I can use that to do my own queries to prevent adding tracks already in the queue-list.

I must get back into SQL queries - its been 15 years since I last got deep into it !

Thanks again,
Chris

[hypadj33]

Would this work for requests? for example excluding certain subcats from being played? when requested? been searching for days to find a way to exclude certain subcats from being played during certain shows.