Frustrations

[Derek]

As I'm sure a lot of us already know, when using SQL queries selecting multiple tracks in a rotation, and when building playlists, you can often have 2 of the same artist etc close together in the list, because the item has not yet actually played.

Having been dipping into learning the (so far) very basics of working with SQL, I was actually making progress (all in a test environment!) by creating an extra date column in the database called 'selected' in the same date format as the other date columns and getting the date stamped when a track was put into the playlist. (In simple terms, SQL add a track, then update the selected date/time stamp for each music track selected before choosing the next one.) I really thought I was beginning to get there until:

1. Running the part of the script that updated the selected date/time threw an exception error only in the rotation editor when 'Run Query' was clicked to test it, BUT it DID still correctly update the selected date in the database. (Worked ok as a direct instruction in HeidiSQL, no error). I thought I could live with the exception warning as long as it was doing it for the show we wanted to use it for. But...
2. This exception seems to block the rest of the rotation instructions as nothing is added to the playlist when running the whole rotation unless I remove those update instructions from the rotation. (I'm assuming as it's asking it to perform an operation outside RadioDJ from the rotation, that might be the reason for the exception warning).
3. Trying to run instructions in HeidiSQL to populate the queuelist table seemed to work until I looked at RadioDJ where the playlist area stayed blank so it seems to only recognise items generated from within RadioDJ itself in the queuelist.

At this point, after spending hours experimenting, I've now hit those blocks and have to admit defeat. However, if mentioning this would help anyone more advanced to able to take the idea forward, then it would have been worthwhile.

Some may be wondering "why bother?" but, if for example we wanted to play 2 tracks from a year or artist, I'd like to avoid tracks from that year or artist for the rest of the hour, otherwise the feature is a bit less value if a few other tracks are from that year or artist too. There's no option for not from year etc. currently in the rotation editor.
Also, the theory (if it could be made to work) could be used to solve the problem when generating playlists of having repeated selection, because the tracks had not yet played, because you'd be able to include the selected date in the repeat rules.

The reason for the subject title is that SQL seems to give a lot of varying possibilities to make RadioDJ work even more tailored to what you need to do, over the already excellent capabilities that it already has. My frustration is, although I like and enjoy a challenge, I have that brick wall I don't seem to be able to knock down to make the idea work :-)

[Kiwi]

Does this help to trigger ideas?...

https://www.youtube.com/watch?v=xgqwHGtHL84

[Derek]

Thanks Kiwi, I was trying to develop that idea my using more SQL to avoid the chosen year or artist etc. for the rest of the hour, but of course then we get repeating artists or years because it's not recognising what's already been inserted into the playlist when choosing the next ones. The example in the video is great for setting the feature though

[stevewa]

try posting your sql code

[Derek]

try posting your sql code

Ok, this is the bit that selects a track and works ok in the RadioDJ rotation editor:

-- 70's song not from featured year.
SELECT `ID`, `artist`, `title`, `year` FROM `songs` WHERE $ForceRepeatRules$ and `song_type` = 0 and `id_subcat`<>46 and `id_subcat`<>80  and `enabled` = 1 AND `year` >=1970 AND `year` <=1979 AND `year` <> `ftyear` AND (TIMESTAMPDIFF(MINUTE, `songs`.`selected`, NOW()) > 120) ORDER BY rand() LIMIT 1;

The `ftyear` is actually another column I added to keep the featured year (set by a previous instruction) throughout the show selections (as I found that a variable seemed to lose its value setting after one query) to either choose from or exclude that particular year and that's not part of this problem.  This selected one track in the rotation editor successfully.

This next bit is where I hit the exception in the rotation editor BUT runs ok in HeidiSQL and still seems to correctly update the selected column when run as a query test for this line alone in the rotation editor, even though it throws the exception:

UPDATE `songs`, `queuelist` SET `songs` . `selected` =  now()  WHERE `songs` . `artist` = `queuelist` . `artist`;

The problem is, I can't use this line in a rotation as the rotation fails (and RadioDJ needs a restart!). I had to include queuelist in the update statement at the beginning otherwise HeidiSQL didn't like it, probably as I was referring to it later even though at this point I wasn't actually updating the queuelist with this statement.

This is what I was trying to use to populate the queuelist with HeidiSQL (It wouldn't work without album being selected too) but although after a refresh in HeidiSQL, it's there in the queuelist, nothing appears in the playlist area queue in RadioDJ:

INSERT INTO `queuelist` (`artist`,`title`,`album`)
SELECT `artist`,`title`,`album` FROM `songs` WHERE  `id_subcat`=3 AND `selected`>(TIMESTAMPDIFF(MINUTE,`selected`, NOW()) > 120)
LIMIT 1;

I know there are a lot more selection parameters that could be used in these, but I was trying the minimum to get it working first.

[stevewa]

make a new rotation
1 row only
using SQL query
select single track, from 70s, forcerepeatrules, and song.id NOT IN (select songid from queuelist)
save rotation, name it "1 70s song not in QL with repeatrules" 

then make a manual event.

make an event action, using Load Track Rotation.
load that new rotation, to TOP of queuelist.


then, create a 1 second audio file, and after import into RadiodDJ, edit track, tab = OTHER, and attach the manual event to that track (under Others tab).

Then put that 1 sec audio file track just imported, into your main rotation programming where you want the 70s song to appear.

when that track plays (1 sec of audio) it will fire the manual event to load the rotation, select 1 70s song which is not in queuelist and forces repeat rules so artist, title, album is not played in last 60 minutes etc


========
my experience has been that you can insert all you want into the database table queuelist, but RadioDJ doesn't do anything with it, i.e. it won't put it into the queuelist window in RadioDJ.

you also can't update 2 tables in 1 statement, but if the above concept of manual event works, then you won't need to update the "selected" column field in the songs table.

[Derek]

my experience has been that you can insert all you want into the database table queuelist, but RadioDJ doesn't do anything with it, i.e. it won't put it into the queuelist window in RadioDJ.

you also can't update 2 tables in 1 statement, but if the above concept of manual event works, then you won't need to update the "selected" column field in the songs table.

Thanks Steve, The comment about RadioDJ ignoring changes you make to the queuelist, that seems to be the case for other things as well that don't seem to get changed like those threshold levels that you gave me a work around for.

I followed your above instructions and got it working to add a track at the top of the list. I'd like to be able to achieve an AutoDJ playlist that can avoid or meet the criteria such as year etc. instead of having one item at a time, so that you could announce what's coming up and have a teaser, have them mix etc. but maybe it's just a limitaiton of how it works at the moment.

When I tried expanding that idea, duplicating the SQL statements to add more tracks within the rotation, it seemed to be ok but then I saw 2 different Leo Sayer tracks with one near the bottom. UPDATE: I'm thinking this might be due to a different piece of code used for the double play from a year. I'll keep experimenting.

Thanks for your help as it works with your suggestion to add a track to the top that avoids or meets the criteria

[stevewa]

depending on how many rows your main rotation adds to the queuelist, the additional rows which are creating the songle row rotation insert might be running first, then the main rotation runs again to add more rows to the queuelist, and it is picking artists and or titles which haven't been played yet so they get picked for the queuelist.

the ForceRepeatRules should be working for history of Artists and Titles, but I think you had a comment which said you tried expanding the NOT IN () code to also include Artist name in queuelist, which should work in theory, but it the main rotation runs again before that artist was played, it could pick that Artist again and d put them at the bottom of the queuelist.

[Derek]

the ForceRepeatRules should be working for history of Artists and Titles, but I think you had a comment which said you tried expanding the NOT IN () code to also include Artist name in queuelist, which should work in theory, but it the main rotation runs again before that artist was played, it could pick that Artist again and d put them at the bottom of the queuelist.

Just an update to this as I've been playing around with the code and ... good news.... having tried it over a few weeks,  getting the required result. (hopefully I've not jinxed it by saying that!).
I used the idea of the single line rotation and worked that into the other rotation but part of the code checked the queuelist LEFT OUTER JOIN `queuelist`
  ON (`songs`.`ID`=`queuelist`.`songid`)
  WHERE `queuelist`.`songid`IS NULL
and along with the force repeat rules and other parts of the code, ensuring that the limit for each query is always 1, it is now not selecting duplicates in the rotation and I'm able to select some from the featured year and others excluded correctly.

It would still be great if we could get the idea around a selected timestamp implemented somehow, so that it could be used for generating preset playlists without it selecting duplicates because they haven't yet played, but at least what I was trying to achieve 'seems' to be working Thanks for all the advice as it gave me a lot of pointers and ways to look at the problem that I hadn't thought of.
It just shows what experimenting and help from this great forum can do
.... Not so frustrated now lol!