Two for Tuesday Event...

[Valdis]

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.

[PROducer]

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 Expand

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]

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

[sammeyers22]

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]

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 Expand

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

[Chaos Radio!]

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.

[eradiodj]

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 Expand

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;

[Chaos Radio!]

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