Rotation by Queries Examples (for RadioDJ 1.7.7+)

[Marius]

Since not all users are familiar with SQL language, i will try to leave here some query examples.
First, open the rotation editor and add a "Track from SQL Query" entry to the rotation. Now double click that entry.
The SQL editor should appear.

My first example is something that was asked recently:

SAMPLE #1: PLAY TWO TRACKS FROM THE SAME ARTIST.

Paste the following query to the editor and save it.

Code (sql) Select Expand

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;

SAMPLE #1 V2 THIS WILL ALSO EXCLUDE ARTISTS THAT ARE IN THE QUEUE

Code Select Expand


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;


The query will search for enabled tracks that are song type and where artists has more than one tracks available. It also applies the user repeat rules to the results.
This can be a separate rotation, loaded using an event for example when you want to play this kind of format, or it can be added to an existing rotation.

As a rule, please remember that any query you add as a rotation entry MUST return at least the track ID.

Please let's keep this topic clean and discuss problems or suggestions on other topics.
Thanks.

UPDATE 1 Jul. 2016:

The track types in database are the following:

Code Select Expand

        Music = 0
        Jingle = 1
        Sweeper = 2
        Voiceover = 3
        Commercial = 4
        InternetStream = 5
        Other = 6
        VDF = 7
        Podcast = 8
        Request = 9
        News = 10
        PlaylistEvent = 11
        FileByDate = 12
        NewestFromFolder = 13
        Teaser = 14

[Marius]

SAMPLE #2: ADD SPECIFIC TRACK IN PLAYLIST

Code Select Expand

SELECT `ID` FROM `songs` WHERE `ID`=X;

X must be replaced with the actual track ID. The track ID's can be seen in tracks manager. If it's not visible, it can be enabled in plugin manager - tracks manager options.

[Marius]

#SAMPLE #3: ADD ALL TRACKS FROM A SPECIFIC ALBUM TO THE PLAYLIST

Code Select Expand

SELECT `ID` FROM `songs` WHERE `album`='term' ORDER BY `track_no` ASC;

term = actual album name (eg. 'Diamonds And Pearls').

Note: The term must be preserved in single quotation mark and its not case sensitive.

[Marius]

SAMPLE #4: ADD 10 TRACKS FROM AN ARTIST TO THE PLAYLIST

Code Select Expand

SELECT `ID` FROM `songs` WHERE `artist`='term' LIMIT 10;

term = actual artist name (eg. 'Prince').
For any other number of tracks, you can change LIMIT 10 to other value, eg. LIMIT 5.

Note: The term must be preserved in single quotation mark and its not case sensitive.

[Marius]

SAMPLE #5: ADD 10 MOST PLAYED TRACKS TO THE PLAYLIST

Code Select Expand

SELECT `ID` FROM `songs` WHERE `song_type`=0 AND `enabled`=1 ORDER BY `count_played` DESC LIMIT 10;

[onno1980]

Find between years..

Code Select Expand

SELECT `ID` FROM `songs` WHERE year BETWEEN '1995' AND '1999' LIMIT 10;

[onno1980]

next one.

one that looks in your subcategorie.

Code Select Expand

SELECT `ID` FROM `songs`WHERE `id_subcat`='47' LIMIT 10

47 is the nummer in my subcategorie.

[onno1980]

Here i have one that looks in your subcategorie (59 is the nummer of mij day subcat). He looks for enabled en your proiryti, than he looks if the nummer is playing..

Code Select Expand

SELECT `ID`, `artist` FROM `songs` WHERE `id_subcat`=59 AND `enabled`=1 AND `weight`=75 ORDER BY `date_played` ASC LIMIT 1;

[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. I've put this example together very quickly, there might be a better way of doing this.

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.

Code Select Expand

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;

[Filip83]

TRACK FROM SEARCH QUERY

This thing will pull 1 track that has a specific word (this case "search_string") in any of the following fields: "path, publisher, copyright".
Think of it as a track from search query function. Just replace "search_string" with whatever search term you want to use. Just like if you use search window in RDJ.

For example: C:/Database/music/good music 80's/Michael Jackson's Best Album/Michael Jackson - Awesome song.mp3

- Search for an artist named "Michael Jackson" that can be a part of a full path and/or filename. *Note that if the filename is michael_jackson that song won't qualify!
- Replace "search_string" with "good music 80's" and every song in that folder will qualify for playing.
- Set "Michael Jackson's Best Album" and all songs from that album will qualify for playing.

This thing also looks in the id3 tags of the entries. It looks for publisher and copyright but that can be modified or removed.

It also enforces the track and artist repeat rules.
In addition it will only pull a track from specific subcategory (please refer to sql for your ids or remove line) = AND `id_subcat` =46
It will only pull music (no jingles and other stuff) = AND `song_type`=0
Only enabled songs. = AND `enabled`=1
Will not add tracks that are already present in the playlist. = AND  `queuelist`.`artist` IS NULL
It will pull the least recently played song by date. = ORDER BY `date_played` ASC
1 song only but could be more than 1 = LIMIT 1;

Code Select Expand

#INSERT HERE ANY NAME YOU WANT FOR THIS ENTRY
SELECT `songs`.`ID`, `songs`.`artist` FROM `songs`
LEFT JOIN `queuelist` ON ( `songs`.`artist` = `queuelist`.`artist`)
WHERE (`path` LIKE '%search_string%' OR `publisher` LIKE '%search_string%' OR`copyright` LIKE '%search_string%' )
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 `id_subcat` =46
AND `song_type`=0
AND `enabled`=1
AND  `queuelist`.`artist` IS NULL
ORDER BY `date_played` ASC
LIMIT 1;

Full credit for this goes to Sr. Member: TQ
Thanks!
Original thread: http://www.radiodj.ro/community/index.php?topic=8358.0


I used this for Twitch.com streaming purposes (game streaming platform). At the time they only allowed audio from the "Monstercat" record label. If any other songs were used on the stream audio would get muted automatically. This query would add "Monstercat" tracks from the database. I used this string in the rotation while I was streaming gameplay to Twitch.

The query searches for the term "Monstercat" inside: Path (folders on hdd), file name, publisher, copyright

If it finds that term anywhere a track would qualify for a rotation. This query will play the same tracks that get displayed when you type "Monstercat" in the search window. Now just replace "search_string" with "Monstercat" or whatever thing you may like to "find" and play. It could be used to play tracks from a specific record label (provided the label is mentioned anywhere in the above fields) or maybe to play tracks from a specific folder, etc...
I am using sub categories as moods. I would make a few entries of this string with different subcategories to change moods of the playing material.

[Valdis]

This is a re-post from this thread as suggested by Filip83. I hope mods won't mind.

These two SQL queries solve issues described by OP:

1. I am running into an issue with my "Heavy Currents" music subcategory occasionally, (every 4-6 hours), not having enough music (Due to artist separation).. since no song is available to rotate, it just drops the category and doesn't schedule anything, so then the hour ends up short. I know the easy solution is to just add more music, but then that would completely blow up how often the songs in the heavy currents rotate. Remember, I only need 1 extra song every 4-6 hours. So... with an SQL string, I would like to build a rotation element that will rotate the Heavy Currents Music Subcategory, but if a song from that subcategory is not available, then rotate a song from "Stay Currents". Make sense?
I.E. If a specific category has nothing to rotate, then schedule from a different named category.

2. I don't want songs from the same genre to rotate more than 2 in a row. I know I can control this somewhat in the rotation builder, but by scheduling a specific genre at a specific slot on the rotation, you essentially assign a handful of songs to always rotate at that same point in the hour, which can make you sound repetitive... I do not want this. I want the freedom for any genre to rotate at anytime, but no more than 2 of the same genre in a row. Is this possible with an SQL string?

1. While SQL doesn't provide any way to determine if query returned value and run another query if needed, there is a solution. It can be done by combining results of two queries using UNION and then selecting just one of those results.

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.

Code Select Expand


-- 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
ORDER BY `date_played`
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 1)

ORDER BY `date_played`
LIMIT 2
)temp
ORDER BY `preference`
LIMIT 1;

Tested and it works. Just remember to change `id_subcat`numbers to correspond to your database.


2. That is quite complicate to do using SQL. The best I can come up with is selecting tracks that don't have same genre as last two tracks in playlist.
This query selects least recently played track from category ID 44 and excludes genres of last two tracks in playlist:

Code Select Expand


-- 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.

[Valdis]

Here is one SQL query I have in my HeidiSQL snippets folder.

This query will select one track from any category with genre name that matches "alternative ", "punk" or "rock" and does not match "pop" or "folk". It can be adapted to your needs by changing the RLIKE regular expression rule strings and adding category filtering rules.

Code Select Expand


-- Select one track from some genres and all categories
SELECT songs.ID, songs.artist, songs.title, genre.name AS genre
FROM songs
LEFT JOIN genre ON(songs.id_genre = genre.id)
LEFT JOIN `queuelist` ON (songs.ID = queuelist.songID OR songs.artist = queuelist.artist)
WHERE `songs`.`enabled`=1
AND songs.song_type=0

-- Genres filter
AND genre.name RLIKE '(alternative |punk|rock)' AND genre.name NOT RLIKE '(pop|folk)'

AND ((`start_date` <= Now()) AND (`end_date` >= NOW() OR `end_date` = '2002-01-01 00:00:01'))
AND (`queuelist`.`songID` IS NULL OR `queuelist`.`artist` IS NULL)
AND (TIMESTAMPDIFF(MINUTE, `date_played`, NOW()) > 240 AND TIMESTAMPDIFF(MINUTE, `artist_played`, NOW()) > 240)
AND TIMESTAMPDIFF(MINUTE, `tartist_played`, NOW())>10

ORDER BY `date_played` ASC, RAND()
LIMIT 1;


[Valdis]

SQL query to select Top 10 most played tracks of a defined period - month, year or week.

You can get Top N played only if history table contains data for the time period you need. The query will be relatively slow, because history table does not contain reference to track ID and you have to JOIN on artist and title columns to get song IDs.

This query will get Top 10 tracks for July 2016:

Code Select Expand


-- 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;


There is one issue with this query - it is not possible to include data for tracks that have been deleted from songs table, even if the data is present in history table. That's also caused by lack of track ID in history table.

[tnz75]

Hi,

In addition to the ID, is it possible to return some options ?
In particular i'm interested in setting sweeper / voice over for the returned track...

[molnarb83]

I would like a query to insert 18 songs to playlist with a specific subcat and genre... but I would like to take care about Artist repeat rule at the song which the query insert.... sometimes put same artist after in this 18 song... this is the query, but it doesn't work... this care about the played artist and doesn't take at the 18 what query generated.... any idea?

Code Select Expand

SELECT * FROM `songs` LEFT JOIN `queuelist` ON (`songs`.`ID` = `queuelist`.`songID` OR `songs`.`artist` = `queuelist`.`artist`) WHERE id_subcat IN ('1','2','4','5','48','79') AND `id_genre`=150 AND (TIMESTAMPDIFF(MINUTE, `date_played`, NOW())>$TrackRepeatInterval$ AND TIMESTAMPDIFF(MINUTE, `artist_played`, NOW())>$ArtistRepeatInterval$) AND (`queuelist`.`songID` IS NULL OR `queuelist`.`artist` IS NULL) ORDER BY `date_played` LIMIT 18;