Author Topic: Two for Tuesday Event...  (Read 11402 times)

Valdis

  • Hero Member

  • Offline
  • *****
  • 562
  • Personal Text
    Radio is a mental condition
    • home
Re: Two for Tuesday Event...
« Reply #30 on: March 25, 2016, 03:49:57 PM »
For the record ordering by the date will not produce any real randomness. Though it might help it appear to be so. If you want more randomness try this:

ORDER by RAND()


From memory as I'm not looking at my two-fer query, this might be needed in both select statements. The first select statement provides an artist. That also needs to be random. Then the nested select statement produces two song titles. Well, whatever number your limit restricts it to. Randomness here couldn't hurt either.

YMMV

Yes, YMMV. And mine did vary. I tried adding ORDER BY RAND() to subquery but for some strange reason the main query started acting weird and didn't select any tracks or selected just one. I don't know why that was happening despite the subquery working every time with random sorting. Maybe internally the RAND() in subquery is evaluated twice due to query optimisation, which leads to two different results and main query can't select any tracks.
I lost few hours of sleep last night over this but still could not figure it out.

To make it work I had to replace ORDER BY RAND() with (sometimes faster) alternative ORDER BY MD5( CONCAT( songs.ID, CURRENT_TIMESTAMP ) ) in subquery. Not exactly random but it works and provides a different result every second.
I am, therefore I think.
I design and develop web apps, and have created few RadioDJ plugins - https://axellence.lv/downloads/

PROducer

  • Hero Member

  • Offline
  • *****
  • 587
  • Personal Text
    Long Time Radio Guy
    • The Rooster
Re: Two for Tuesday Event...
« Reply #31 on: January 16, 2017, 08:48:26 AM »
I'm running two-fors every couple of hours, and have a working query, but the problem is that it typically picks two songs from the same album.  Is there a way to make sure the songs are from different albums?

My query is below.

Code: [Select]
SELECT `ID`, `artist` FROM `songs` WHERE `artist`=(
SELECT `artist` FROM `songs`
WHERE `enabled`=1
AND `song_type`=0
AND `id_subcat`=47
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 `id_subcat`=47
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;

Capt_Fuzzy

  • Hero Member

  • Offline
  • *****
  • 1078
  • Personal Text
    "Quiet numbskulls, I'm broadcasting!" ~ Moe Howard
    • WVRR - Ridgerunners Radio
Re: Two for Tuesday Event...
« Reply #32 on: January 16, 2017, 09:13:58 AM »
Generally, a "two for tuesday" is by the same artist, so getting two from the same album is kind of normal in this case.
Hang in there, I'm sure one of the SQL gurus around here will be able to tell you how to do it... ;)
Steve 'Capt Fuzzy' Wade
Proud & Satisfied "Long-term" RadioDJ User
(Currently making the transition to v2)

WVRR - Ridgerunners Radio
The best mix of your favorites!

sammeyers22

  • New User

  • Offline
  • *
  • 26
Re: Two for Tuesday Event...
« Reply #33 on: April 02, 2017, 12:01:34 AM »
I got mine to work off all of the posts and it does load everything. but there is one problem. It loads it all in alphabetical order by artist and song title. How do I get it to stop doing that. Thanks

eradiodj

  • Jr. Member

  • Offline
  • **
  • 55
    • PureRock.US - America's Pure Rock
Re: Two for Tuesday Event...
« Reply #34 on: April 11, 2020, 09:11:17 PM »
I am able to Chaos Radio's query to load and run but I cannot figure our how to add an ID or liner between the two songs it chooses and load.

Here is what's working for me....

Any help would be greatly appreciated.

Code: [Select]
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
PureRock.US - America's Pure Rock
http://www.purerock.us

Chaos Radio!

  • Full Member

  • Offline
  • ***
  • 153
Re: Two for Tuesday Event...
« Reply #35 on: April 12, 2020, 11:48:38 PM »
So to get the liner to play in-between just do it in the Track Rotations.

1st line Track from SQL Query - TWOFER Script
2nd line - ID
3rd line Track from SQL Query - TWOFER Script
4th line - ID

They are in reverse orders because the ID will place on top of the playlist. I repeated the lines twice just to have more on the playlist. I  am still using 1.8.2 version of RadioDJ I believe you might need to save the first line you add as a new rotation. Then re-open it and add the other lines I know there is a weird issue with SQL multiple lines in 1.8.2.
 
Chaos Radio! - True Punk Radio for True Punk Rockers!
Over 100,000 songs in the music vault from all over the globe! Proud RadioDJ user since 2014.

https://chaosradio.info

eradiodj

  • Jr. Member

  • Offline
  • **
  • 55
    • PureRock.US - America's Pure Rock
Re: Two for Tuesday Event...
« Reply #36 on: April 18, 2020, 04:12:41 PM »
So to get the liner to play in-between just do it in the Track Rotations.

1st line Track from SQL Query - TWOFER Script
2nd line - ID
3rd line Track from SQL Query - TWOFER Script
4th line - ID

They are in reverse orders because the ID will place on top of the playlist. I repeated the lines twice just to have more on the playlist. I  am still using 1.8.2 version of RadioDJ I believe you might need to save the first line you add as a new rotation. Then re-open it and add the other lines I know there is a weird issue with SQL multiple lines in 1.8.2.

Thanks for the response. Much appreciated. I am also running 1.82 and I understand your suggestion of leveraging track rotation, but I was hoping to setup a singular event that would load song, sweep, song at one time.

Is it possible to modify my working query to include a sweeper inside of it?

Code: [Select]
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 (`id_subcat`=30 OR `id_subcat`=31)
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()) > 100) AND (TIMESTAMPDIFF(MINUTE, `songs`.`artist_played`, NOW()) > 100))
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 (`id_subcat`=30 OR `id_subcat`=31)
AND ((`start_date` <= Now()) AND (`end_date` >= NOW() OR `end_date` = '2002-01-01 00:00:01'))
AND ((TIMESTAMPDIFF(MINUTE, `date_played`, NOW()) > 100) AND (TIMESTAMPDIFF(MINUTE, `artist_played`, NOW()) > 100))
ORDER BY `date_played` ASC
LIMIT 2;
PureRock.US - America's Pure Rock
http://www.purerock.us

Chaos Radio!

  • Full Member

  • Offline
  • ***
  • 153
Re: Two for Tuesday Event...
« Reply #37 on: April 21, 2020, 04:55:42 PM »
Yes, you can add it to the twofer Tuesday sql script, but you need to add it as a Union. The union is multiple commands in the sql.

More explanation is here, I have never tried to do that but the examples should help:

https://www.radiodj.ro/community/index.php?topic=9626.0
Chaos Radio! - True Punk Radio for True Punk Rockers!
Over 100,000 songs in the music vault from all over the globe! Proud RadioDJ user since 2014.

https://chaosradio.info