SQL Query Help

[Nitrofish]

Jhonny was nice enough to help me with the following query, but did not have the time to test it and see if it works because he was dealing with some personal issues. Thanks Jhonny for trying.

The query does not work however and just pops up the default error message "Your query does not return anything, or contains errors!", and I am having a devil of a time trying to figure out why. I was hopeful someone better at SQL queries than I am could help. The query is supposed to pull a certain number of tracks from a specific artist, using the track played rule. I play 60 minutes of a specific artist (Rush) on Saturday afternoons. Can someone have a look at this query and tell me what the problem is please?

Code Select Expand



SELECT `songs`.`ID`, `songs`.`artist`, `songs`.`title` FROM `songs` 
LEFT JOIN `queuelist` ON (`songs`.`ID` = `queuelist`.`songID` OR `songs`.`artist` = `queuelist`.`artist`)  WHERE `songs`.`enabled`=1 AND `artist`= rush  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$)  AND (`queuelist`.`songID` IS NULL OR `queuelist`.`artist` IS NULL) ORDER BY RAND() LIMIT 10;



I would use the sample provided by Darius Marius, but it picks artist at random. Where would I modify it to make it pick a specific artist from a specific sub category?

Code Select Expand



SELECT `ID`, `artist` FROM `songs` WHERE `artist`=(
SELECT `artist` FROM `songs`
WHERE `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$))
GROUP BY `artist`
HAVING COUNT(*) > 1
ORDER BY `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
LIMIT 2;



Thanks.

[Marius]

I don't know any Darius on the forum, but a few things i noticed:

Code Select Expand

`artist`= rush
Whenever you deal with a string, you must enclose it in single quotes, so it will become:

Code Select Expand

`artist`= 'rush'
Then, artist is ambiguous because you've selected 2 tables (songs and queuelist), so you must specify exactly which you are using:

Code Select Expand

`artist`= 'rush'
becomes

Code Select Expand

`songs`.`artist`= 'rush'

The final tested code is this:

Code Select Expand

SELECT `songs`.`ID`, `songs`.`artist`, `songs`.`title` FROM `songs` 
LEFT JOIN `queuelist` ON (`songs`.`ID` = `queuelist`.`songID` OR `songs`.`artist` = `queuelist`.`artist`)  WHERE `songs`.`enabled`=1 AND `songs`.`artist`= 'Chris Brown'  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$)  AND (`queuelist`.`songID` IS NULL OR `queuelist`.`artist` IS NULL) ORDER BY RAND() LIMIT 10;

[Nitrofish]

Sorry Marius. Brain fart there. Thanks so much for the response. I tested your query in MySQL Workbench 6.5 and changed only the artist from Chis Brown to Rush, but it returns nothing and gives an error. I then tested it in the Rotation Creator and it worked fine. Do you know why that is?

I was doing all my testing in the Workbench, and assumed if it would work in there, it would work in the rotation creator.

Thanks for your time.

[TQ]

In answer to your question about Workbench, both $TrackRepeatInterval$ and $TrackRepeatInterval$ are RDJ program variables not SQL variables so workbench cannot see them.

This is quite a useful script which I will use but I noticed it has a couple of pitfalls when I tested it.

It is dependent on no tracks from that artist (in your case Rush) being previously played for the period covered by your rotation rules. Mine are set quite high ie 450 & 120 respectively which could cause the script to fail if a track from that artist plays within that period. At the very least you need to put something else in the rotation for it to fall back to or it'll trigger RDJ's 'Silence' response which would be less predictable.

Another is that the script limits itself to 10 tracks (LIMIT 10) which could be a problem as, when it restarts, you will have the artist in the range of the:-

Code Select Expand

AND (TIMESTAMPDIFF(MINUTE, `date_played`, NOW())>$TrackRepeatInterval$ AND TIMESTAMPDIFF(MINUTE, `artist_played`, NOW())>$ArtistRepeatInterval$)

10 x four minute tracks will not fill your hour. Both problems could be resolved by removing the above section of the code or the latter by changing the LIMIT 10 to something well in excess of the hour you intend to run it for.

TQ

[Nitrofish]

That makes sense. Thanks. What about if I wanted to select those Rush Tracks from a specific sub category?

For instance I have a sub category name "Normal Rotation" that contains hits from artists, and then another sub category named "Deep Cuts" that contains, well... deep cuts from artists.

Ideally I would like to select two tracks from specific artist in "Normal Rotation", and then one from same artist in the "Deep Cut" sub category.

[TQ]

That's a little more complicated, this is how I've worked out how to do it but I suspect there is a better way, I'm learning too!

You first need to know the ID of the sub categories. These you will find in the subcategory table.

I have also trimmed the scripts to avoid collisions with the 'rotation rules', it will now only limit itself to tracks that have not been played in the past 60 minutes plus the other obvious limits eg enabled etc.. The sub_cats I'm using are 6 & 41 in the example. I've also modified the:-

Code Select Expand

`songs`.`artist`= 'rush'

to

Code Select Expand

`songs`.`artist`LIKE '%rush%'

The % symbol is a wildcard which allows a bit of flexibility in the Artist name so that (in your case) an artist of 'Rush' or 'Rush ft. xxx' and 'xxx ft. Rush' would all be included in the query result. You can undo this change if you'd prefer exact artist naming.

So, our rotation now has 2 queries, the first will select 2 tracks from 'Pink Floyd' from the sub_cat 41 that have not been played in the past 60 minutes. The second will select 1 track from 'Pink Floyd' from sub_cat 6, also not having been played in the past 60 minutes.

Query 1

Code Select Expand

SELECT `songs`.`ID`, `songs`.`artist`, `songs`.`title` FROM `songs` 
LEFT JOIN `queuelist` ON (`songs`.`ID` = `queuelist`.`songID` OR `songs`.`artist` = `queuelist`.`artist`) 
WHERE `songs`.`enabled`=1
AND `songs`.`artist` LIKE '%pink floyd%'
AND `songs`.id_subcat = 41
AND ((`start_date` <= Now()) AND (`end_date` >= NOW() OR `end_date` = '2002-01-01 00:00:01')) 
AND TIMESTAMPDIFF(MINUTE, `date_played`, NOW())> '00:60:00'
ORDER BY RAND() LIMIT 2;

Query 2

Code Select Expand

SELECT `songs`.`ID`, `songs`.`artist`, `songs`.`title` FROM `songs` 
LEFT JOIN `queuelist` ON (`songs`.`ID` = `queuelist`.`songID` OR `songs`.`artist` = `queuelist`.`artist`) 
WHERE `songs`.`enabled`=1
AND `songs`.`artist` LIKE '%pink floyd%'
AND `songs`.id_subcat = 6
AND ((`start_date` <= Now()) AND (`end_date` >= NOW() OR `end_date` = '2002-01-01 00:00:01')) 
AND TIMESTAMPDIFF(MINUTE, `date_played`, NOW())> '00:60:00'
ORDER BY RAND() LIMIT 1;


This can now be repeated at infinitum until you run out of tracks that fall outside of the imposed limits of the script.

Does that cover it?

TQ

[Nitrofish]

Awesome TQ thanks! I will give them a workout next weekend and see how the do. Stay tuned! 

[Nitrofish]

What is the story with the end date in this line?

Code Select Expand

AND ((`start_date` <= Now()) AND (`end_date` >= NOW() OR `end_date` = '2002-01-01 00:00:01'))

[TQ]

2002-01-01 00:00:01 is a never played 'default' date

TQ

[TQ]

Can I suggest that you try the queries before air-time on a non-production machine to be sure that nothing unexpected happens eg the wildcard artist name may be an issue to you or you run out of tracks etc.

TQ

[Nitrofish]

Can I suggest that you try the queries before air-time on a non-production machine to be sure that nothing unexpected happens eg the wildcard artist name may be an issue to you or you run out of tracks etc.

TQ

I removed the wild card. I am playing Rush in the 10 O'clock hour, and Led Zeppelin in the 11 O'clock hour, and neither of them has any featured artists. I did of course try it on a test machine and it worked fine for 60 minutes.

[Nitrofish]

Twofer Tuesday Query.

I want to play 2 songs from a random artist from a specific sub category and enforce the track repeat rule all day Tuesday. I tried using Marius' example:

Code Select Expand



SELECT `ID`, `artist` FROM `songs` WHERE `artist`=(
SELECT `artist` FROM `songs`
WHERE `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$))
GROUP BY `artist`
HAVING COUNT(*) > 1
ORDER BY `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
LIMIT 2;



It works, but the artist played repeat interval keeps it from running too long even though I have more than 300 artists. Can you help me adjust it so that it will keep picking "least recently played" track from a random artist, in a specific sub category, and enforce track played rule, but not artist played for say 90 minutes please?

[TQ]

If I understood the question correctly you should do the following:

Under:

Code Select Expand

AND `songs`.`song_type`=0

add

Code Select Expand

AND `id_subcat`=41
where 41 is replaced by your chosen sub_cat

then replace

Code Select Expand

$ArtistRepeatInterval$

with

Code Select Expand

'00:90:00'
as we did in the previous script.

Note: There is more than one entry for these items so you have to repeat the above.

I haven't tested the above so if you run into problems, let me know.

TQ

[Nitrofish]

Awesome thanks. It works, but I would need to put a sweeper in between each query, and that is a bit annoying for the listener. I tried just adding the query twice to the rotation, but it just added 4 songs from the same artist. I would like to select 4 random songs from two different artists in a specific sub cat, then a sweeper. Any ideas?

[TQ]

What you are asking for highlights a weakness of this script IMHO ie it always selects the first artist that meets the criteria.

I just can't get my head around how to RAND() the selected artist at the moment (cos I'm a novice at this) so my best offer right now is this:

Insert the query into the rotation twice followed by the sweeper.

Query 1 would be as per, the second query:-

change both instances of

Code Select Expand

`date_played`

for

Code Select Expand

`count_played

I've tested this and it works but it's not 100% perfect as it's still not random.

You could of course change it for 'weight' DESC (highest weighting) or replacing DESC for ASC (for lowest weighting: likely most often played) or even 'play_limit' ASC, that's up to you.

TQ