RadioDJ v2.0.2.0 Beta Released!

[Calypso]

That is true, I don't take the associated artists into consideration. For that I would need to put all that information into the database, which is an enormous task.

Be aware that the recent beta versions don't use the artist seperation anymore when determining that "UB40 & Chrissie Hynde" are 2 artists, it uses the associated artist field which can be populated through the Utilities. So taking the associated artist field into consideration is (in my opinion) a "must".

Also, populating it can be a daunting task, yes, but in my database it's already done (50.000 items). And no, not by hand.

[AdrianHondema]

Hi Calypso,

Thank you for mentioning the utilities: I had not spotted the 'Associated artists' one! Will try it out this weekend and if it works okay (which I'm sure it will) I will alter my query!

Cheers,
Adrian

[AdrianHondema]

Hiya,

I used the 'Associated artists' utility and changed my query as below. Seems to work fine!

/*
Select a random track from the top2000 category where:
1. the track is valid for the current date
2. the track has not yet been played today
3. the artist has not been played in the last 60 minutes
4. the artist is not already in the queuelist
5. with the lowest count_played
*/
SELECT * FROM songs
 WHERE songs.enabled = 1 /* enabled */
     AND songs.song_type = 0 /* Music */
     AND songs.id_subcat = 33 /* Top2000 */
     AND ((`songs`.`start_date` <= Now()) AND (`songs`.`end_date` >= NOW() OR `songs`.`end_date` = '2002-01-01 00:00:01')) /* only tracks that are valid for the current date */
     AND (TIMESTAMPDIFF(MINUTE, songs.date_played, NOW()) > 3600) /* don't play the same track twice a day */
     AND (TIMESTAMPDIFF(MINUTE, songs.artist_played, NOW()) > 60) /* don't play the same artist twice an hour */
     AND NOT songs.artist in (select queuelist.associated_artists from queuelist)
     AND count_played = (select min(count_played) from songs where id_subcat = 33) /* select the least played tracks */
 ORDER BY RAND()
  LIMIT 1

[AdrianHondema]

Hi,

I noticed this error in my error-log:

Database Insert Error: FUNCTION radiodj2.REGEXP_INSTR does not exist (CALL UpdateTracks2(30826,'Katie Melua','Spider\'s Web',0,0,30,0);
UPDATE `songs` SET `artist_played`=NOW() WHERE REGEXP_INSTR(songs.associated_artists, '\\b(Katie Melua)\\b');
)

RadioDJ Version 2.0.2.0. MySQL Version 5.7.

Any idea what causes this??

[DJ Garybaldy]

Just a hunch but It could be MySQL 5.7 at fault I'd suggest you upgrade to something like MariaDB.

Backup any databases before changing over.

[AdrianHondema]

Hi Marius,

It was indeed MySQL 5.7 that caused the error. I upgraded to v8 and now the error no longer occurs.

Adrian

[Marius]

Thank you for the update. If however will return, please let me know.

[Calypso]

Hiya,

I used the 'Associated artists' utility and changed my query as below. Seems to work fine!

/*
Select a random track from the top2000 category where:
1. the track is valid for the current date
2. the track has not yet been played today
3. the artist has not been played in the last 60 minutes
4. the artist is not already in the queuelist
5. with the lowest count_played
*/
SELECT * FROM songs
 WHERE songs.enabled = 1 /* enabled */
     AND songs.song_type = 0 /* Music */
     AND songs.id_subcat = 33 /* Top2000 */
     AND ((`songs`.`start_date` <= Now()) AND (`songs`.`end_date` >= NOW() OR `songs`.`end_date` = '2002-01-01 00:00:01')) /* only tracks that are valid for the current date */
     AND (TIMESTAMPDIFF(MINUTE, songs.date_played, NOW()) > 3600) /* don't play the same track twice a day */
     AND (TIMESTAMPDIFF(MINUTE, songs.artist_played, NOW()) > 60) /* don't play the same artist twice an hour */
     AND NOT songs.artist in (select queuelist.associated_artists from queuelist)
     AND count_played = (select min(count_played) from songs where id_subcat = 33) /* select the least played tracks */
 ORDER BY RAND()
  LIMIT 1

Yeah, getting there, but with the "in" statement you are looking for a exact match of the artist name compared to the contents of associated_artists, while "associated_artists" can be a set of artists. So what I've done is using the find_in_set function in MySQL, and that is nearly there.

However - it seems that there is still a risk for multiple entries. I'm using the setting that there must always be 2 full rotations in the queue. Somehow it looks like RadioDJ only writes the complete rotation in the queuelist at a time - so when you query the queuelist, the previous entries (from this rotation) aren't in it yet. @Marius: is this how it currently works?

BTW: I'd use the $ForceRepeatRules$ macro instead of hardcoding the repeat rules in the query - when you want to change the time artists (or titles or whatever) are not repeated, you have to change all the rules. With $ForceRepeatRules$ it's taken into account rightaway.

[andrei1412]

I'm wondering what's happening with these songs from the same artist in a very short time? (Jax Jones)
I'm testing on my machine, not on air. On the stable version this isn't happening.

[AdrianHondema]

Hi Calypso,

Thank you for your help, much appreciated!
I see what you mean: that subquery will never lead to a result. I changed my query as below:

SELECT * FROM songs
 WHERE songs.enabled = 1 /* enabled */
   AND songs.song_type = 0 /* Music */
   AND songs.id_subcat = 33 /* Top2000 */
   AND ((`songs`.`start_date` <= Now()) AND (`songs`.`end_date` >= NOW() OR `songs`.`end_date` = '2002-01-01 00:00:01')) /* only tracks that are valid for the current date */
   AND (TIMESTAMPDIFF(MINUTE, songs.date_played, NOW()) > 3600) /* don't play the same track twice a day */
   AND (TIMESTAMPDIFF(MINUTE, songs.artist_played, NOW()) > 120) /* don't play the same artist twice an hour */
   AND (SELECT count(*) FROM queuelist WHERE FIND_IN_SET(songs.artist, queuelist.associated_artists) = 0) /* Associated artist not in queuelist */
   AND count_played = (select min(count_played) from songs where id_subcat = 33) /* select the least played tracks */
 ORDER BY RAND()
 LIMIT 1

[AdrianHondema]

Andrei,
The slash is not in the sepator list:
,
;
f.
ft.
feat.
featuring,
&
and
and the

[AdrianHondema]

Hi Calypso,

There still is one issue though: when I copy the query in my previous post to the tracks rotation and run the query it returns an error and does not select a record. The problem is in the FIND_IN_SET part: when it's commented out the query is ok and returns a record. Could it be that this funxtion is not supported by RadioDJ.

Adrian

[AdrianHondema]

Ahhh, my own fault: got the brackets wrong:

AND (SELECT count(*) FROM queuelist WHERE FIND_IN_SET(songs.artist, queuelist.associated_artists)) = 0

[andrei1412]

Andrei,
The slash is not in the sepator list:
,
;
f.
ft.
feat.
featuring,
&
and
and the

I added the separtor in the slash list, and associated artists looks like in the screenshot. The problem is that it treats artists like this X / Y / Z as separate artists from X, Y, Z
I really don't know what i'm doing wrong 

[AdrianHondema]

There was one other issue with my query... Artists like 'Hatfield & The North' are seperated as 'Hatfield' and 'The North' in the assiciated artist: 'hatfield, the north'. When you then check the artistname against the associated artists there is no match: 'Hatfield & The North' is not the same as 'hatfield' or 'the north'. So, instead of only checking the associated artists, you will have to check the artist name as well.

This is the final version of my query:
SELECT * FROM songs
 WHERE songs.enabled = 1 /* the track is enabled */
   AND songs.song_type = 0 /* Music */
   AND songs.id_subcat = 33 /* Top2000 */
   AND ((`songs`.`start_date` <= Now()) AND (`songs`.`end_date` >= NOW() OR `songs`.`end_date` = '2002-01-01 00:00:01')) /* only tracks that are valid for the current date */
   AND (TIMESTAMPDIFF(MINUTE, songs.date_played, NOW()) > 3600) /* don't play the same track twice a day */
   AND (TIMESTAMPDIFF(MINUTE, songs.artist_played, NOW()) > 120) /* don't play the same artist twice an hour */
   AND NOT songs.artist IN (SELECT artist FROM queuelist) /* Artist not in queuelist */
   AND (SELECT COUNT(*) FROM queuelist WHERE FIND_IN_SET(songs.artist, queuelist.associated_artists)) = 0 /* Associated artist not in queuelist */
   AND count_played = (SELECT MIN(count_played) FROM songs WHERE id_subcat = 33) /* select the least played tracks */
 ORDER BY RAND()
 LIMIT 1