Author Topic: Adding RANDOM in Tandem with DAY of Week in SELECTING Songs  (Read 534 times)

kaypee808

  • New User

  • Offline
  • *
  • 23
Adding RANDOM in Tandem with DAY of Week in SELECTING Songs
« on: January 19, 2017, 05:46:23 PM »
Thanks to ruvo80's post in http://www.radiodj.ro/community/index.php?topic=9626.msg55642.

His post [using CURDATE()] has guided me to solve my previous question, though in a longer way. I am sharing the solution here for SQL experts to improve on it, or make a shorter version of it:

#LOVE SONGS English
SELECT * FROM (
(SELECT songs.ID, songs.artist, songs.title, songs.weight, songs.count_played, songs.date_played, 'no' AS `from_requests`, 1 AS `preference`  FROM songs
LEFT JOIN queuelist ON (songs.artist = queuelist.artist OR songs.ID = queuelist.songID)
                WHERE start_date <= NOW()
                AND (end_date >= NOW() OR end_date = '2002-01-01 00:00:01')
                AND song_type = 0
                AND enabled = 1
                AND TIMESTAMPDIFF(MINUTE, `date_played`, NOW()) > 500
                AND TIMESTAMPDIFF(MINUTE, `artist_played`, NOW()) > 30
                AND `path` LIKE '%Love Songs English%'
                AND `count_played`<5
      AND DAYNAME(CURDATE()) = 'Monday'
                AND `weight` >90
                AND queuelist.artist IS NULL
                AND queuelist.songID IS NULL
GROUP BY `artist`
HAVING COUNT(*) <2
ORDER BY RAND()
LIMIT 2)

UNION

(SELECT songs.ID, songs.artist, songs.title, songs.weight, songs.count_played, songs.date_played, 'no' AS `from_requests`, 2 AS `preference`  FROM songs
LEFT JOIN queuelist ON (songs.artist = queuelist.artist OR songs.ID = queuelist.songID)
                WHERE start_date <= NOW()
                AND (end_date >= NOW() OR end_date = '2002-01-01 00:00:01')
                AND song_type = 0
                AND enabled = 1
                AND TIMESTAMPDIFF(MINUTE, `date_played`, NOW()) > 500
                AND TIMESTAMPDIFF(MINUTE, `artist_played`, NOW()) > 30
                AND `path` LIKE '%Love Songs English%'
                AND `count_played`<5
      AND DAYNAME(CURDATE()) = 'Tuesday'
                AND `weight` BETWEEN 70 AND 80
                AND queuelist.artist IS NULL
                AND queuelist.songID IS NULL
GROUP BY `artist`
HAVING COUNT(*) <2
ORDER BY RAND()
LIMIT 2)

UNION

(SELECT songs.ID, songs.artist, songs.title, songs.weight, songs.count_played, songs.date_played, 'no' AS `from_requests`, 3 AS `preference`  FROM songs
LEFT JOIN queuelist ON (songs.artist = queuelist.artist OR songs.ID = queuelist.songID)
                WHERE start_date <= NOW()
                AND (end_date >= NOW() OR end_date = '2002-01-01 00:00:01')
                AND song_type = 0
                AND enabled = 1
                AND TIMESTAMPDIFF(MINUTE, `date_played`, NOW()) > 500
                AND TIMESTAMPDIFF(MINUTE, `artist_played`, NOW()) > 30
                AND `path` LIKE '%Love Songs English%'
                AND `count_played`<5
      AND DAYNAME(CURDATE()) = 'Wednesday'
                AND `weight` BETWEEN 51 AND 60
                AND queuelist.artist IS NULL
                AND queuelist.songID IS NULL
GROUP BY `artist`
HAVING COUNT(*) <2
ORDER BY RAND()
LIMIT 2)

UNION

(SELECT songs.ID, songs.artist, songs.title, songs.weight, songs.count_played, songs.date_played, 'no' AS `from_requests`, 4 AS `preference`  FROM songs
LEFT JOIN queuelist ON (songs.artist = queuelist.artist OR songs.ID = queuelist.songID)
                WHERE start_date <= NOW()
                AND (end_date >= NOW() OR end_date = '2002-01-01 00:00:01')
                AND song_type = 0
                AND enabled = 1
                AND TIMESTAMPDIFF(MINUTE, `date_played`, NOW()) > 500
                AND TIMESTAMPDIFF(MINUTE, `artist_played`, NOW()) > 30
                AND `path` LIKE '%Love Songs English%'
                AND `count_played`<5
      AND DAYNAME(CURDATE()) = 'Thursday'
                AND `weight` BETWEEN 60 AND 70
                AND queuelist.artist IS NULL
                AND queuelist.songID IS NULL
GROUP BY `artist`
HAVING COUNT(*) <2
ORDER BY RAND()
LIMIT 2)

UNION

(SELECT songs.ID, songs.artist, songs.title, songs.weight, songs.count_played, songs.date_played, 'no' AS `from_requests`, 5 AS `preference`  FROM songs
LEFT JOIN queuelist ON (songs.artist = queuelist.artist OR songs.ID = queuelist.songID)
                WHERE start_date <= NOW()
                AND (end_date >= NOW() OR end_date = '2002-01-01 00:00:01')
                AND song_type = 0
                AND enabled = 1
                AND TIMESTAMPDIFF(MINUTE, `date_played`, NOW()) > 500
                AND TIMESTAMPDIFF(MINUTE, `artist_played`, NOW()) > 30
                AND `path` LIKE '%Love Songs English%'
                AND `count_played`<5
      AND DAYNAME(CURDATE()) = 'Friday'
                AND `weight` BETWEEN 75 AND 95
                AND queuelist.artist IS NULL
                AND queuelist.songID IS NULL
GROUP BY `artist`
HAVING COUNT(*) <2
ORDER BY RAND()
LIMIT 2)

UNION

(SELECT songs.ID, songs.artist, songs.title, songs.weight, songs.count_played, songs.date_played, 'no' AS `from_requests`, 6 AS `preference`  FROM songs
LEFT JOIN queuelist ON (songs.artist = queuelist.artist OR songs.ID = queuelist.songID)
                WHERE start_date <= NOW()
                AND (end_date >= NOW() OR end_date = '2002-01-01 00:00:01')
                AND song_type = 0
                AND enabled = 1
                AND TIMESTAMPDIFF(MINUTE, `date_played`, NOW()) > 500
                AND TIMESTAMPDIFF(MINUTE, `artist_played`, NOW()) > 30
                AND `path` LIKE '%Love Songs English%'
                AND `count_played`<5
      AND DAYNAME(CURDATE()) = 'Saturday' OR 'Sunday'
                AND `weight` =50
                AND queuelist.artist IS NULL
                AND queuelist.songID IS NULL
GROUP BY `artist`
HAVING COUNT(*) <2
ORDER BY RAND()
LIMIT 2)

UNION

(SELECT songs.ID, songs.artist, songs.title, songs.weight, songs.count_played, songs.date_played, 'no' AS `from_requests`, 7 AS `preference` FROM songs
LEFT JOIN queuelist ON (songs.artist = queuelist.artist OR songs.ID = queuelist.songID)
                WHERE start_date <= NOW()
                AND (end_date >= NOW() OR end_date = '2002-01-01 00:00:01')
                AND song_type = 0
                AND enabled = 1
                AND TIMESTAMPDIFF(MINUTE, `date_played`, NOW()) > 400
                AND TIMESTAMPDIFF(MINUTE, `artist_played`, NOW()) > 30
                AND `path` LIKE '%Love Songs English %'
                AND `count_played`<3
                AND `weight` = 50
                AND queuelist.artist IS NULL
                AND queuelist.songID IS NULL
GROUP BY `artist`
HAVING COUNT(*) <2
ORDER BY `date_played` ASC, RAND()
LIMIT 2)

)
temp
ORDER BY `preference`
LIMIT 2

***ORIGINAL POST***

Even in SQL with UNION, the songs seem to repeat everyday, unless you really stretch the duration of the SongTitle separation. How about if a CASE scenario is added to concentrate on certain range of 'weight' (called priority) everyday without having to create an event for each day of the weekdays. What I would like to achieve is: if Monday, select songs based on all the criteria listed, especially with weight "so and so"; if Tuesday, with weight "so on and so forth", etc.

Here is the code I am working on to make it work (please help me on the separated section in the middle):

Note: Without the separated section, the SQL should work. But if I have a show that repeats Monday to Friday, I do not want to write an event for each day, if it can be automated by basing it on the day name (e.g., Monday, Tuesday, etc.) of the date on the computer at the time of play.

Code: [Select]
SELECT * FROM (
(SELECT songs.ID, songs.artist, songs.title, songs.weight, songs.count_played, songs.date_played, 'no' AS `from_requests`, 1 AS `preference`  FROM songs
LEFT JOIN queuelist ON (songs.artist = queuelist.artist OR songs.ID = queuelist.songID)
                WHERE start_date <= NOW()
                AND (end_date >= NOW() OR end_date = '2002-01-01 00:00:01')
                AND song_type = 0
                AND enabled = 1
                AND TIMESTAMPDIFF(MINUTE, `date_played`, NOW()) > 500
                AND TIMESTAMPDIFF(MINUTE, `artist_played`, NOW()) > 30
                AND `path` LIKE '%Love Songs English %'
                AND `count_played`<5


      AND SELECT DAYNAME(NOW())
       CASE WHEN 'weekday' = Monday THEN
                AND `weight` > 90
        WHEN 'weekday' = Tuesday THEN
                AND `weight` BETWEEN 70 AND 80
        WHEN 'weekday' = Wednesday THEN
        AND `weight` BETWEEN 51 AND 60
        WHEN 'weekday' = Thursday THEN
        AND `weight` BETWEEN 60 AND 70
        WHEN 'weekday' = Friday THEN
        AND `weight` BETWEEN 75 AND 95
        ELSE 'weekday' = Saturday OR Sunday
         END AS `weight` = 50


                AND queuelist.artist IS NULL
                AND queuelist.songID IS NULL
GROUP BY `artist`
HAVING COUNT(*) <2
ORDER BY RAND()
LIMIT 2)

UNION

(SELECT songs.ID, songs.artist, songs.title, songs.weight, songs.count_played, songs.date_played, 'no' AS `from_requests`, 2 AS `preference` FROM songs
LEFT JOIN queuelist ON (songs.artist = queuelist.artist OR songs.ID = queuelist.songID)
                WHERE start_date <= NOW()
                AND (end_date >= NOW() OR end_date = '2002-01-01 00:00:01')
                AND song_type = 0
                AND enabled = 1
                AND TIMESTAMPDIFF(MINUTE, `date_played`, NOW()) > 400
                AND TIMESTAMPDIFF(MINUTE, `artist_played`, NOW()) > 30
                AND `path` LIKE '%Love Songs English %'
                AND `count_played`<3
                AND `weight` = 50
                AND queuelist.artist IS NULL
                AND queuelist.songID IS NULL
GROUP BY `artist`
HAVING COUNT(*) <2
ORDER BY RAND()
LIMIT 2)

)
temp
ORDER BY `preference`
LIMIT 2

[/color]
Thanks again!