• Fine Tuning RadioDJ
  • Importing Music
  • For radiodj use Stand alone encoders
  • The query rotation how to make
    Explanation by rovu80 (A radiodj forum member).

    Hi guys, First of all.. I typed this message in word pad 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 script writing 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 working this forum is stunning. Everyone is willing to help each other 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 treat 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 together and found a great solution 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 query.. 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 separately 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()) <= 8)
    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 blocks:

    #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 do not 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

    radiodj.info: Many thanks Ruud