RadioDJ tutorials


    Query for rotations

    Just a note: -- in frond of a line is just comment, and has no effect on the query.

    [ By Marius: ]

    PLAY TWO TRACKS FROM THE SAME ARTIST.

    SELECT `ID`, `artist` FROM `songs` WHERE `artist`=( SELECT `artist` FROM `songs` WHERE `enabled`=1
    AND `song_type`=0 AND ((`start_date` <= Now()) AND (`end_date` >= NOW() OR `end_date` = '2002-01-01 00:00:01'))
    AND ((TIMESTAMPDIFF(MINUTE, `date_played`, NOW()) > $TrackRepeatInterval$)
    AND (TIMESTAMPDIFF(MINUTE, `artist_played`, NOW()) > $ArtistRepeatInterval$)) GROUP BY `artist` HAVING COUNT(*) > 1
    ORDER BY `date_played` ASC LIMIT 1) AND `enabled`=1 AND `song_type`=0 AND ((`start_date` <= Now())
    AND (`end_date` >= NOW() OR `end_date` = '2002-01-01 00:00:01')) AND ((TIMESTAMPDIFF(MINUTE, `date_played`, NOW()) >
    $TrackRepeatInterval$) AND (TIMESTAMPDIFF(MINUTE, `artist_played`, NOW()) > $ArtistRepeatInterval$)) ORDER BY `date_played`
    ASC LIMIT 2;
    

    THIS WILL ALSO EXCLUDE ARTISTS THAT ARE IN THE QUEUE

    SELECT `ID`, `artist` FROM `songs` WHERE `artist`=( SELECT `songs`.`artist` FROM `songs` LEFT JOIN `queuelist`
    ON ( `songs`.`artist` = `queuelist`.`artist`) WHERE `songs`.`enabled`=1 AND `songs`.`song_type`=0
    AND ((`songs`.`start_date` <= Now()) AND (`songs`.`end_date` >= NOW() OR `songs`.`end_date` = '2002-01-01 00:00:01'))
    AND ((TIMESTAMPDIFF(MINUTE, `songs`.`date_played`, NOW()) > $TrackRepeatInterval$)
    AND (TIMESTAMPDIFF(MINUTE, `songs`.`artist_played`, NOW()) > $ArtistRepeatInterval$))
    AND `queuelist`.`artist` IS NULL GROUP BY `songs`.`artist` HAVING COUNT(*) > 1 ORDER BY `songs`.`date_played`
    ASC LIMIT 1) AND `enabled`=1 AND `song_type`=0 AND ((`start_date` <= Now()) AND (`end_date` >= NOW()
    OR `end_date` = '2002-01-01 00:00:01')) AND ((TIMESTAMPDIFF(MINUTE, `date_played`, NOW()) > $TrackRepeatInterval$)
    AND (TIMESTAMPDIFF(MINUTE, `artist_played`, NOW()) > $ArtistRepeatInterval$))
    ORDER BY `date_played` ASC LIMIT 2;
    

    SELECT TRACK FROM PLAYLIST, ENFORCING ARTIST AND ALBUM REPEAT RULES.

    SELECT `songs`.`ID`, `songs`.`artist`, `songs`.`title` FROM `songs` LEFT JOIN `queuelist`
    ON (`songs`.`ID` = `queuelist`.`songID` OR `songs`.`artist` = `queuelist`.`artist`)
    LEFT JOIN `playlists_list` ON (`songs`.`ID` = `playlists_list`.`sID`) WHERE `songs`.`enabled`=1
    AND ((`start_date` <= Now()) AND (`end_date` >= NOW() OR `end_date` = '2002-01-01 00:00:01'))
    AND (TIMESTAMPDIFF(MINUTE, `date_played`, NOW())>$TrackRepeatInterval$
    AND TIMESTAMPDIFF(MINUTE, `artist_played`, NOW())>$ArtistRepeatInterval$)
    AND (`queuelist`.`songID` IS NULL OR `queuelist`.`artist` IS NULL) AND (`playlists_list`.`pID` = '4')
    ORDER BY RAND() LIMIT 1;
    
    In this example, `playlists_list`.`pID` = '4' specifies the ID of the playlist.
    To find the ID of your chosen playlist look in the ID field in the playlists table of your database.



    [ By beavis: ]

    Select track from playlist, enforcing artist and album repeat rules:

    With this example you can choose a track from a playlist, rather than a category. The advantage of selecting from playlists instead of categories is that a track can be placed in multiple playlists, whereas it can only be placed in a single category.
    SELECT `songs`.`ID`, `songs`.`artist`, `songs`.`title` FROM `songs`
    LEFT JOIN `queuelist` ON (`songs`.`ID` = `queuelist`.`songID` OR `songs`.`artist` = `queuelist`.`artist`)
    LEFT JOIN `playlists_list` ON (`songs`.`ID` = `playlists_list`.`sID`)
    WHERE `songs`.`enabled`=1
    AND ((`start_date` <= Now()) AND (`end_date` >= NOW() OR `end_date` = '2002-01-01 00:00:01'))
    AND (TIMESTAMPDIFF(MINUTE, `date_played`, NOW())>$TrackRepeatInterval$ AND TIMESTAMPDIFF(MINUTE, `artist_played`, NOW())>$ArtistRepeatInterval$)
    AND (`queuelist`.`songID` IS NULL OR `queuelist`.`artist` IS NULL)
    AND (`playlists_list`.`pID` = '4')
    ORDER BY RAND() LIMIT 1;
    
    In this example, `playlists_list`.`pID` = '4' specifies the ID of the playlist. To find the ID of your chosen playlist look in the ID field in the playlists table of your database.


    [ By Valdis: ]

    This SQL query will select least recently played track from category ID 44 and fall back to category ID 25 if first query doesn't return a result.

    -- Select songs from one category with fallback to second category if first query fails to return a result.
    SELECT * FROM (
            SELECT `songs`.`ID`, `songs`.`artist`, `songs`.`title`, `songs`.`date_played`, `songs`.`id_subcat`, 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` = 44
            AND (`songs`.`start_date` <= NOW() OR `songs`.`start_date` = '2002-01-01 00:00:01')
            AND (`songs`.`end_date` >= NOW() OR `songs`.`end_date` = '2002-01-01 00:00:01')
            AND (TIMESTAMPDIFF(MINUTE, `songs`.`date_played`, NOW()) > $TrackRepeatInterval$)
            AND (TIMESTAMPDIFF(MINUTE, `songs`.`artist_played`, NOW()) > $ArtistRepeatInterval$)
            AND  `queuelist`.`artist` IS NULL
            LIMIT 1
    
        UNION
    
            SELECT `songs`.`ID`, `songs`.`artist`, `songs`.`title`, `songs`.`date_played`, `songs`.`id_subcat`, 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` = 25
            AND (`songs`.`start_date` <= NOW() OR `songs`.`start_date` = '2002-01-01 00:00:01')
            AND (`songs`.`end_date` >= NOW() OR `songs`.`end_date` = '2002-01-01 00:00:01')
            AND (TIMESTAMPDIFF(MINUTE, `songs`.`date_played`, NOW()) > $TrackRepeatInterval$)
            AND (TIMESTAMPDIFF(MINUTE, `songs`.`artist_played`, NOW()) > $ArtistRepeatInterval$)
            AND  `queuelist`.`artist` IS NULL
    
        ORDER BY `date_played`
        LIMIT 2
    )temp
    ORDER BY `preference`
    LIMIT 1;
    
    Just remember to change `id_subcat`numbers to correspond to your database.

    This query selects least recently played track from category ID 44 and excludes genres of last two tracks in playlist:

    -- Select song from category and avoid selecting song with same genre as last two tracks in queuelist.
    SELECT `songs`.`ID`, `songs`.`artist`, `songs`.`title`, `songs`.`id_genre` FROM `songs`
    LEFT JOIN `queuelist` ON ( `songs`.`artist` = `queuelist`.`artist`)
    
    LEFT JOIN (
        SELECT `songs`.`id_genre` FROM `queuelist`
        LEFT JOIN `songs` ON (`songs`.`ID` = `queuelist`.`songID`)
        ORDER BY queuelist.ID DESC
        LIMIT 2
    ) AS queue_genre ON (songs.id_genre = queue_genre.id_genre)
    
    WHERE `songs`.`enabled` = 1
    AND `songs`.`song_type` = 0
    
    AND `songs`.`id_subcat` = 44
    
    AND (`songs`.`start_date` <= NOW() OR `songs`.`start_date` = '2002-01-01 00:00:01')
    AND (`songs`.`end_date` >= NOW() OR `songs`.`end_date` = '2002-01-01 00:00:01')
    AND (TIMESTAMPDIFF(MINUTE, `date_played`, NOW()) > $TrackRepeatInterval$)
    AND (TIMESTAMPDIFF(MINUTE, `artist_played`, NOW()) > $ArtistRepeatInterval$)
    AND  `queuelist`.`artist` IS NULL
    
    AND queue_genre.id_genre IS NULL
    
    ORDER BY `date_played` ASC
    LIMIT 1
    
    Just change `id_subcat` to match needed category ID. If you use this query multiple times in a rotation, it will disallow tracks with same genre to be played side by side.
    This query will get top ten tracks for July:

    Note: You can get top whatever played only if history table contains data for the time period you need.

    
    -- Select Top10 tracks from July 2016
    SELECT songs.ID, songs.artist, songs.title, COUNT(*) AS spins, SUM(history.listeners) as total_listeners
    FROM `songs`
    LEFT JOIN `history` ON (songs.title = history.title AND songs.artist = history.artist)
    
    -- This is the date range filter
    WHERE history.date_played BETWEEN '2016-07-01' AND '2016-08-01'
    
    AND songs.song_type = 0
    
    GROUP BY history.title, history.artist
    ORDER BY spins DESC
    LIMIT 10;
    

    -- Select 2 tracks on bpm between XX and XX
    SELECT `songs`.`ID`, `songs`.`artist`, `songs`.`title`, `songs`.`id_genre` FROM `songs`
    LEFT JOIN `queuelist` ON ( `songs`.`artist` = `queuelist`.`artist`)
    
    LEFT JOIN (
        SELECT `songs`.`id_genre` FROM `queuelist`
        LEFT JOIN `songs` ON (`songs`.`ID` = `queuelist`.`songID`)
        ORDER BY queuelist.ID DESC
        LIMIT 2
    ) AS queue_genre ON (songs.id_genre = queue_genre.id_genre)
    
    WHERE `songs`.`enabled` = 1
    AND `songs`.`song_type` = 0
    
    AND `songs`.`bpm` BETWEEN XX AND XX
    
    AND (`songs`.`start_date` <= NOW() OR `songs`.`start_date` = '2002-01-01 00:00:01')
    AND (`songs`.`end_date` >= NOW() OR `songs`.`end_date` = '2002-01-01 00:00:01')
    AND (TIMESTAMPDIFF(MINUTE, `date_played`, NOW()) > $TrackRepeatInterval$)
    AND (TIMESTAMPDIFF(MINUTE, `artist_played`, NOW()) > $ArtistRepeatInterval$)
    AND  `queuelist`.`artist` IS NULL
    
    AND queue_genre.id_genre IS NULL
    
    ORDER BY `date_played` ASC
    LIMIT 2
    
Back to top
©  JWL  2013 | 2017