Random Select from category and then play in series

[sothdfm1]

So basically I have a rotation that plays songs forever.
Whenever I want to play something else I set up an event.
It loads the track in top of queue and when the track is finished the rotation keeps playing songs (it has already loaded songs in the queue).

In my case I will have 4 different set of courses.
(don't worry about that - first I want to make it work for one set of courses and I will do the same for the other sets).

I want a specific hour of the day (different hour for each set of courses) to load a track based on the pattern I have described:
The first day load one random course then play all its tracks in order for the next days until all of the tracks are played. Then repeat this pattern.

For the example given above with indexes (https://www.radiodj.ro/community/index.php?topic=14936.msg82600#msg82600):

• Math will play for the first 10 days
• then Physics for the next 12 days
• and then Chemistry for the next 8 days
• and then start over.

This is the pattern but for many more courses.

And something else. I have an intro and an outro track that I would like to play, but I guess they can be loaded with seperate events, if that complicates the SQL script.
If it's possible it would be better to load them from the script (the intro before the lesson and the outro after the lesson (no sweppers or something else needed, just play them as seperate tracks)).

So the event would be: basically
1. Load track (intro)
2. Run SQL query (inserts correct lesson to queue)
3. Load track (outro)
(thats simplified, but that is mostly what it will do, using my query.) It will load the next lesson (courses will be randomized using a different software bulk rename, and mp3tag to set "track #", since courses wont need to be randomized often, I think this is OK. 

[sothdfm1]

So the event would be: basically
1. Load track (intro)
2. Run SQL query (inserts correct lesson to queue)
3. Load track (outro)
(thats simplified, but that is mostly what it will do, using my query.) It will load the next lesson (courses will be randomized using a different software bulk rename, and mp3tag to set "track #", since courses wont need to be randomized often, I think this is OK.

Ok. Here are my step-by-step directions on playing a course every day at a set time.
I hope this is what you were looking for.   

I'm starting with a clean RadioDJ Install, and I have 5 "courses" all with 2 to 6, 1-minute blank mp3 files inside. Named differently. You must not have any "title" be named the same.   

The folder structure on my PC is this:
> Courses
     > Course 1
        > Lesson 1
   > Lesson 2
     > Course 2
   > Lesson 1
   > Lesson 2
   > Lesson 3
     > Course 3
   > Lesson 1
   > Lesson 2
   > Lesson 3
   > Lesson 4
   > Lesson 5
     > Course 4
   > Lesson 1
   > Lesson 2
   > Lesson 3
   > Lesson 4
     > Course 5
   > Lesson 1
   > Lesson 2
   > Lesson 3
   > Lesson 4
   > Lesson 5
   > Lesson 6

I will first open Bulk Rename Utility.
1. Drag your COURSE folder (or equivalent) into bulk rename utility. You should see all of your Course folders in the software now. No mp3s should show.
2. Go to ACTIONS –> LIST –> APPLY RANDOM SORT TO CURRENT LIST. This randomly sorts your courses.
3. In the list of folders in Bulk rename utility, select the folders, highlighting them light blue.
3. Go to the "NUMBERING" section of the software towards the bottom. Have the settings like this:
   Mode: Prefix
   Start: 1
   Pad: 3
   Break: 0
   At: 0
   Incr.: 1
   Sep.: "-" , thats a dash with no quotation mark.

4. Press RENAME –> Press OK. Close the program.
Next we will open mp3tag. This will sort our "Track #" for the rotation.
1. Drag your Course folders in (the ones we just renamed) this should import all of the mp3s.
2. Sort by path ascending.
3. Select all tracks.
4. go to TOOLS → Auto Numbering wizard, Keep at defaults to increase track # by 1.
5. Press OK... Wait to finish, close the program.


Now open RadioDJ.
You can make a new category for courses, then in that category, add a sub-cat called "All". Your track sorted tracks will go here.
IMPORT Folder, select your COURSES folder (that includes the course folders) to your new sub-cat. Select to also import sub directories.
Now your tracks are imported.

Go to your tracks rotation manager. Create a new rotation.
ADD (+) track from SQL Query.
Add the following query:

Code Select Expand

SELECT `ID`, title FROM `songs` WHERE `id_subcat` = 109
AND ((`songs`.`start_date` <= NOW() OR `songs`.`start_date` = '2002-01-01 00:00:01')
AND (`songs`.`end_date` >= NOW() OR `songs`.`end_date` = '2002-01-01 00:00:01')
AND (TIMESTAMPDIFF(MINUTE, `songs`.`date_played`, NOW()) > 0)
AND (TIMESTAMPDIFF(MINUTE, `songs`.`title_played`, NOW()) > 1051200)
AND (TIMESTAMPDIFF(MINUTE, `songs`.`artist_played`, NOW()) > 0))
ORDER BY `track_no` ASC
LIMIT 1;

Change the `id_subcat` = 109 to your subcat that you made with all of your lessons in it.

Save the query, save the rotation.

Make a new event to repeat by day. The items in the event should play your intro, load the rotation to play the lesson, and play your outro after the lesson. I hope you will be able to figure out the event yourself, but if you have problems, or its not working, let me know.

Let me know how this works for you.
   

[Tony316]

Ok I will try it with a few courses and tell you if it worked.

If you could explain in detail what the SQL script does?
(I trust you it's correct, but I don't have experience with SQL and it's an opportunity to learn something new - it might be useful for other things in the future).

[sothdfm1]

Ok I will try it with a few courses and tell you if it worked.

If you could explain in detail what the SQL script does?
(I trust you it's correct, but I don't have experience with SQL and it's an opportunity to learn something new - it might be useful for other things in the future).

Sure! Not a problem.
So Ill break down what it is doing:

Code Select Expand

SELECT `ID`, title FROM `songs` WHERE `id_subcat` = 32

This is selecting an ID (which RadioDJ then knows what track is assigned that ID) from all the tracks in the database, but they must be in a certain subcat, in your case, courses.

Code Select Expand


AND ((`songs`.`start_date` <= NOW() OR `songs`.`start_date` = '2002-01-01 00:00:01')
AND (`songs`.`end_date` >= NOW() OR `songs`.`end_date` = '2002-01-01 00:00:01')
AND (TIMESTAMPDIFF(MINUTE, `songs`.`date_played`, NOW()) > 0)
AND (TIMESTAMPDIFF(MINUTE, `songs`.`title_played`, NOW()) > 1576800)
AND (TIMESTAMPDIFF(MINUTE, `songs`.`artist_played`, NOW()) > 0))

This block here is all for respecting repeat rules. A "title" or track, cannot be played more than once every 3 years (in minutes = 1576800) you should change that if its too much. Basically you should have changed the randomization by then. 

Code Select Expand

ORDER BY `track_no` ASC
LIMIT 1;
Order the ID's you fetched from your subcat, and sort them into track #, thats why we had to set track number in mp3tag. From those ID's you fetched, only 1 will play every rotation (LIMIT 1).

[Tony316]

This block here is all for respecting repeat rules. A "title" or track, cannot be played more than once every 3 years (in minutes = 1576800) you should change that if its too much. Basically you should have changed the randomization by then.

So basically in this block you select tracks all except for those that have been played in the last three years.
And then select the next one (with lowest track number).

Also if you could explain a bit more detailed the second block which selects all except those already played. What does each field mean (maybe its obvious by their names, but I want to be sure).

EDIT:
Two more things I rembered:

• Does the track numbering in mp3tag have to be zero padded in the beggining (001,002,003,...) so as for the SQL script ORDER command to work;
• After looking at my files I am going to have 5 sets of courses with few hundred lessons in total. The number is not a multiple of 365 and also there are years with 366 days, so eventually I may not need to re-index my courses for random selection since no one will notice that - the same lesson won't play again on the same day of the year.

[sothdfm1]

So basically in this block you select tracks all except for those that have been played in the last three years.
And then select the next one (with lowest track number).

Also if you could explain a bit more detailed the second block which selects all except those already played. What does each field mean (maybe its obvious by their names, but I want to be sure).

EDIT:
Two more things I rembered:

• Does the track numbering in mp3tag have to be zero padded in the beggining (001,002,003,...) so as for the SQL script ORDER command to work;
• After looking at my files I am going to have 5 sets of courses with few hundred lessons in total. The number is not a multiple of 365 and also there are years with 366 days, so eventually I may not need to re-index my courses for random selection since no one will notice that - the same lesson won't play again on the same day of the year.

Of course!

Code Select Expand

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

This is checking to make sure the track is enabled in the database, and that it is set to be able to play. The reason is because you can import tracks ahead of time and set them to start playing at a later date/time. This makes sure the track is allowed to play.

Code Select Expand

AND (TIMESTAMPDIFF(MINUTE, `songs`.`date_played`, NOW()) > 0)

The track can be played > 1 time in a day. This WONT happen because of the next one.

Code Select Expand

AND (TIMESTAMPDIFF(MINUTE, `songs`.`title_played`, NOW()) > 1576800)

Track (or title) cant be played more than once in 3 years (1576800minutes = 3 years) this overrules all other conditions.

Code Select Expand

AND (TIMESTAMPDIFF(MINUTE, `songs`.`artist_played`, NOW()) > 0))
The artist of the track can be played > 1 time in a day. This WONT happen because of the `title_played` condition.

The "AND" in the statement is basically just saying add another condition.\


-----------
Edit: Just noticed you edited last comment. Writing in now reply now.

[sothdfm1]

• Does the track numbering in mp3tag have to be zero padded in the beggining (001,002,003,...) so as for the SQL script ORDER command to work;

No I dont think so. You could try with/wo.

• After looking at my files I am going to have 5 sets of courses with few hundred lessons in total. The number is not a multiple of 365 and also there are years with 366 days, so eventually I may not need to re-index my courses for random selection since no one will notice that - the same lesson won't play again on the same day of the year.

Good idea. One thing to note is that if your repeat rules (in the sql query I sent) are too much and they exceed the amount of time it will take for all the lessons to complete, you will have days, or months with no lesson because they have all been played in the past 3 years. Same thing for if the repeat rules are not enough, it will start to start from beginning without finishing all lessons. Let me think about this one, ill see if I can make a script that if no ID is returned, it resets `title_played` in the DB to default, then will repeat without leaving any lessons behind. We will see about that. Ill think about how that would be possible.

Ill keep you updated

[Tony316]

Well I could modify the script for every set of courses and
set the time interval it doesn't play the same song as (minutes of day * number of lessons)
so when all lessons are played then it starts over and don't wait for (e.g. 3 years to pass since the first was played).

For example if I have (200 lessons * 1 lesson / day * 1440 mins / day = 280000 mins instead of 1576800 you set on the script - this is the only parameter that sould be changed right?).

[sothdfm1]

Well I could modify the script for every set of courses and
set the time interval it doesn't play the same song as (minutes of day * number of lessons)
so when all lessons are played then it starts over and don't wait for (e.g. 3 years to pass since the first was played).

For example if I have (200 lessons * 1 lesson / day * 1440 mins / day = 280000 mins instead of 1576800 you set on the script - this is the only parameter that sould be changed right?).

Yea! you could try that.

[Tony316]

Ok so I tested your script in Rotations-> Track from SQL query and Run your query to see if it finds the tracks correctly but it returned an error message that no tracks were found or the SQL script was wrong.
I removed some conditions and run this instead:

Code Select Expand

SELECT `ID`, title FROM `songs`
ORDER BY `track_no` ASC
LIMIT 1;
and it worked fine. It doesn't even recognize `id_subcat`=18.
What seems to be the problem?

EDIT
My fault i had the wrong id. Sorry
Is there way to reset date played? Because I would like to make some tests and then when I set it up I would like to reset all dates played.
Or I could just modify the script to include all tracks from the beggining?
I will experiment with SQL scripts. It's way more powerful than simple events. I'll let you know when I set it up.

[sothdfm1]

Ok so I tested your script in Rotations-> Track from SQL query and Run your query to see if it finds the tracks correctly but it returned an error message that no tracks were found or the SQL script was wrong.
I removed some conditions and run this instead:

Code Select Expand

SELECT `ID`, title FROM `songs`
ORDER BY `track_no` ASC
LIMIT 1;
and it worked fine. It doesn't even recognize `id_subcat`=18.
What seems to be the problem?

EDIT
My fault i had the wrong id. Sorry
Is there way to reset date played? Because I would like to make some tests and then when I set it up I would like to reset all dates played.
Or I could just modify the script to include all tracks from the beggining?
I will experiment with SQL scripts. It's way more powerful than simple events. I'll let you know when I set it up.

The ways I know to do it are to re-import the tracks, or use SQL script which I could try to make if you need (Have done before, but dont remeber what I did lol). But for now you could also set the title_played repeat rules in the query to 0 (which will follow the settings in AutoDJ) just for testing  .

Also, when you are finished setting up and in use, If you stream online, I would love a link if you dont mind .

[Tony316]

Well my stream isn't in English so I probably can't send it to you.
Also I am going to stream mainly on FM radio (not sure if it will be setup for online stream also).

I have just setup the event (it required about 10 commands so as to load intro , load sql rotation and then outro and then reload the previous rotation, but eventually it works).

What remains to be done is to try it on several tracks just to see that it selects them in the correct order (maybe make a rotation with the same SQL script for many times and skipping the lessons to see how they are picked).

Again I would like to thank you very much for your help and your time.
At first I thought I wouldn't be able to resolve my issue, but when there is a community around you can resolve many thiings you couldn't do on your own.

[sothdfm1]

Well my stream isn't in English so I probably can't send it to you.
Also I am going to stream mainly on FM radio (not sure if it will be setup for online stream also).

I have just setup the event (it required about 10 commands so as to load intro , load sql rotation and then outro and then reload the previous rotation, but eventually it works).

What remains to be done is to try it on several tracks just to see that it selects them in the correct order (maybe make a rotation with the same SQL script for many times and skipping the lessons to see how they are picked).

Again I would like to thank you very much for your help and your time.
At first I thought I wouldn't be able to resolve my issue, but when there is a community around you can resolve many thiings you couldn't do on your own.

Thanks for those kind words, .
I hope you are able to get it working for you. Remeber, if you need any help, this community is always helping  .

Thanks.

[Tony316]

[EDIT] Just edited sql script to work when you reach the last track. Instead of

Code Select Expand

MOD(track_no+1,<total_number_of_lessons>) it should be

Code Select Expand

MOD(track_no,<total_number_of_lessons>)+1.
Also note that <total_number_of_lessons> could be replaced by:

Code Select Expand

SELECT COUNT(title) FROM songs WHERE id_subcat = <id_subcat_no> in case the number of tracks changes.
Just tested it on RadioDJ with a rotation using this SQL script and it works fine. I skipped lessons every few seconds (skipping them too fast results in playing the same track some times because the database has not yet been updated) and all lessons played in the correct order. Also when the final lesson is played, lessons start playing from the first one.
I overall think that SQL (or MariaDB) is a powerful tool to adjust your song selection on RadioDJ.
Again congratulations to the creators of the program. I have been using it for almost 5 months in live FM broadcasting and it works fine (except for some occasional lags which are acceptable).
Cheers!
-----------------------------------------------------------------------------------------------------------------------------------------------------
[Original Post]
Hi again,
I recently figured another way to play tracks in the desired order. The SQL query could just play the next lesson based on the track number of the most recently played lesson :

Code Select Expand


SELECT id, title
FROM songs
WHERE
id_subcat = <id_subcat_no>
AND
track_no = ( MOD( (SELECT track_no FROM songs WHERE id_subcat = <id_subcat_no> ORDER BY date_played DESC LIMIT 1),  <total_number_of_lessons>) + 1 )



[Tony316]

Just an update on SQL script in case some tracks are removed so track numbers don't go from 1 to max_tracks. This script does the following:

• Finds track_no of most recently played lesson
• Checks if the track is bigger than the biggest track_no of enabled lessons
• If so then selects the lesson with the smallest track_no from enabled lessons (i.e. starts from the first lesson again)
• Else, the lesson with the next track_no from enabled lessons is selected

Code Select Expand


-- Find track_no of most recently played lesson
SET @current_track :=
(
    SELECT track_no
    FROM songs
    WHERE
    id_subcat = <id_subcat_no>
    AND
    enabled = 1
    ORDER BY date_played DESC
    LIMIT 1
);

-- Select next lesson based on current_track
-- Select the first lesson if all songs are played

SELECT id
FROM songs
WHERE
id_subcat = <id_subcat_no>
AND
track_no =
(
    -- Check if all lessons have been played
    IF
    (
        @current_track >=
        -- Find biggest track_no from enabled lessons
        (
            SELECT
            track_no
            FROM songs
            WHERE
            id_subcat = <id_subcat_no>
            AND
            enabled = 1
            ORDER BY track_no DESC
            LIMIT 1
        ),
        -- Select smallest track_no from enabled lessons
        (
            SELECT track_no
            FROM songs
            WHERE
            id_subcat = <id_subcat_no>
            AND
            enabled = 1
            ORDER BY track_no ASC
            LIMIT 1
        ),
        -- Select next track_no from enabled songs
        (
            SELECT track_no
            FROM songs
            WHERE
            id_subcat = <id_subcat_no>
            AND
            track_no > @current_track
            AND
            enabled = 1
            ORDER BY track_no ASC
            LIMIT 1
         )
    )

);


This way we can enable/disable lessons and still play only the enabled lessons in series without changing the track numbers. However if new lessons are added to the database, their track numbers should be bigger than the maximum track number of all lessons (including disabled ones).