There was one other issue with my query... Artists like 'Hatfield & The North' are seperated as 'Hatfield' and 'The North' in the assiciated artist: 'hatfield, the north'. When you then check the artistname against the associated artists there is no match: 'Hatfield & The North' is not the same as 'hatfield' or 'the north'. So, instead of only checking the associated artists, you will have to check the artist name as well.
This is the final version of my query:
SELECT * FROM songs
WHERE songs.enabled = 1 /* the track is enabled */
AND songs.song_type = 0 /* Music */
AND songs.id_subcat = 33 /* Top2000 */
AND ((`songs`.`start_date` <= Now()) AND (`songs`.`end_date` >= NOW() OR `songs`.`end_date` = '2002-01-01 00:00:01')) /* only tracks that are valid for the current date */
AND (TIMESTAMPDIFF(MINUTE, songs.date_played, NOW()) > 3600) /* don't play the same track twice a day */
AND (TIMESTAMPDIFF(MINUTE, songs.artist_played, NOW()) > 120) /* don't play the same artist twice an hour */
AND NOT songs.artist IN (SELECT artist FROM queuelist) /* Artist not in queuelist */
AND (SELECT COUNT(*) FROM queuelist WHERE FIND_IN_SET(songs.artist, queuelist.associated_artists)) = 0 /* Associated artist not in queuelist */
AND count_played = (SELECT MIN(count_played) FROM songs WHERE id_subcat = 33) /* select the least played tracks */
ORDER BY RAND()
LIMIT 1
169 Replies
20397 Views