Created 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. Just remember to change `id_subcat`numbers to correspond to your database.

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;


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

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


This query will get top ten tracks for July:

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;


This query looks for tracks between two BPM (beats per minutes):

— 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


    Created by Marius:

The 2fer Query- Plays 2 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;


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

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.

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;


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;