X  

Plugins Part II

Track/Directory import

Plugins v.182

Troubleshooting





 ☰  menu
To top
Released on 28 Sep 2018 RadioDJ v2.0.0.9
    DJ-JHONNY.NL || RADIODJ v.2+ TUTORIALS.

Explanation of query's to use in rotations.

Note from Jhonny.
Em not sure if the $TrackRepeatInterval$ and $ArtistRepeatInterval$ still can be used in the new version v.2.0.0.9

The TOTAL query in a rotation:
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 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 explain 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)
 

The explanation:

#Christmas

THIS IS JUST A NAME TO KNOW WHAT SONG WILL BE SELECTED
THE # OR -- MAKES IT COMMENT AND WILL NOT BE SEEN AS A COMMANT.

(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 PREFERENCE 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 SOMETHING 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 (AT THE END) IN THE SCRIPT I WILL SELECT JUST 1 TRACK AS LIMIT!

This is all. But I know you just want 1 song to be added in the rotation not 3 X 20.
Well here is the explanation. At almost the end of the whole query 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:

Almost at the bottom of the total query.

  ORDER BY `date_played` asc
    LIMIT 60
I USED LIMIT 20 IN THE PREVIOUS PART. AS YOU SEE IN THE FULL QUERY I SEARCH FOR 20 TRACKS ON EACH 3 SONG TYPES (CHRISTMAS, SUMMER, 90 POP).
FOR EACH SONG TYPE I LET THE DATABASE SEARCH FOR A TOTAL OF 60 SONGS.
IN THE TEXT ABOVE, THE DATABASE PLACES ALL 60 SONGS IN ORDER DATE OF PLAYED.

IT ORDERS SONGS BY PREFERENCE (LIKE I SHOWED BEFORE) AND DATE PLAYED AS WELL.
AND FINALLY IT LEAVES JUST 1 SONG TO ADD TO THE ROTATION!!!
    )
temp
ORDER BY `preference`, `date_played` asc
LIMIT 1
It is quite some explanation text but I really hope it is use full for you all.
Again, in this opportunity 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 unnecessarily disabled songs in your database.

Sours RadioDJ forum member: ruvo80
To top