QUERY'S

List of query's to use in rotations.

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;