Help with SQL string

[Skydragon980]

Hello, i'm searching a solution to create a commercial rotation to use in radio dj, looking over the rotation rules i found one named SQL Query, so how can i made a query that, for example, play a random commercial the "Paid Commercial" section that has not been played more than 10 times?

and another question, is there a way to reset that count everyday at midnight?

[Filip83]

This is very interesting!

The 1st part should be relatively easy for some forum members. I hope they will see this post. You can check this thread for examples:http://www.radiodj.ro/community/index.php?topic=6658.msg50604#msg50604
Let's see if a pro can pick this up. Otherwise I might be able to scrape together a string for you.

As for resetting the play count that could be done with Events.

Add an event that will run once every day that will use plugin action > PluginAction|Utilities|ResetPlaycounts But you probably need to add some arguments.
Again I can't help to reduce the reset to a specific category,... I'm sure it can be done.

Also I have been told there's 3rd party apps that are made to run commercials somehow but then again I have no need for commercials and hence I don't really know much on the subject.

Let's see if we can get some more feedback on the subject and then decide what to do. If we decide to use the SQL thingy We will need to know the "ID" of the "category" OR "track type" and any other criteria you need to use to select from the correct pool.

[Filip83]

I'm assuming every block of commercials will have an "intro" and "outro" jingle. So what happens if there is no more Commercials to be played that day? We have to be sure that at least 1 commercial will be pulled from the database. Maybe make the last entry "least times played" or "least recently played".

This is just something you have to consider and think about. The what if scenario.

[Skydragon980]

I'm assuming every block of commercials will have an "intro" and "outro" jingle. So what happens if there is no more Commercials to be played that day? We have to be sure that at least 1 commercial will be pulled from the database. Maybe make the last entry "least times played" or "least recently played".

This is just something you have to consider and think about. The what if scenario.

you can put a number of playing even to the intro and outro for example if you have enough commercial to make 10 spot cluster so the intro and outro will have 10 play count when they'll reach 0 the server will skip the commercial cluster.

anyway if you can help me with the "SQL Thing" i will appreciate it

[Filip83]

That would not work as jingles don't usually follow repeat rules. I don't know how to do that. If there is no commercials to play the intro and outro would still play.

Insert intro jingle here

Code: [Select]

#INSERT TITLE HERE
SELECT `songs`.`ID`, `songs`.`artist` FROM `songs`
LEFT JOIN `queuelist` ON ( `songs`.`artist` = `queuelist`.`artist`)
WHERE `song_type`=4
AND `enabled`=1
AND `count_played`<10
AND  `queuelist`.`artist` IS NULL
ORDER BY `date_played` ASC
LIMIT 5;

insert outro jingle here

try it. no guarantees as I am pretty much a noob!

Note that the commercials will play the least recently played as I think that works better than random.
Make sure your commercials are in fact song type 4. This is probably true but they could use a different id.
There should be no duplicate "artist" in the queue list. This means that the same spot should not repeat more than 1x in a block.
There will be a maximum of 5 spots added to the queue.

please report.

Additionally I think there might be an option to write a complex single entry that would cover intro and outro jingles and add conditions to only load if there is actual commercials to play. But that's way out my reaches.

[Capt_Fuzzy]

That would not work as jingles don't usually follow repeat rules. I don't know how to do that. If there is no commercials to play the intro and outro would still play.

Insert intro jingle here

Code: [Select]

#INSERT TITLE HERE
SELECT `songs`.`ID`, `songs`.`artist` FROM `songs`
LEFT JOIN `queuelist` ON ( `songs`.`artist` = `queuelist`.`artist`)
WHERE `song_type`=4
AND `enabled`=1
AND `count_played`<10
AND  `queuelist`.`artist` IS NULL
ORDER BY `date_played` ASC
LIMIT 5;

insert outro jingle here

The way events load, that would not quite be right...
When loading events to the top of the que, they tend to load in reverse order, so setup your event like this:

Load outro
Load SQL Query
Load intro

When the event runs, the outro will be loaded first, followed by the query, and then the intro, so it will actually look like this in the que:

intro
SQL Query
outro

[Filip83]

[Skydragon980]

That would not work as jingles don't usually follow repeat rules. I don't know how to do that. If there is no commercials to play the intro and outro would still play.

Insert intro jingle here

Code: [Select]

#INSERT TITLE HERE
SELECT `songs`.`ID`, `songs`.`artist` FROM `songs`
LEFT JOIN `queuelist` ON ( `songs`.`artist` = `queuelist`.`artist`)
WHERE `song_type`=4
AND `enabled`=1
AND `count_played`<10
AND  `queuelist`.`artist` IS NULL
ORDER BY `date_played` ASC
LIMIT 5;

insert outro jingle here

try it. no guarantees as I am pretty much a noob!

Note that the commercials will play the least recently played as I think that works better than random.
Make sure your commercials are in fact song type 4. This is probably true but they could use a different id.
There should be no duplicate "artist" in the queue list. This means that the same spot should not repeat more than 1x in a block.
There will be a maximum of 5 spots added to the queue.

please report.

Additionally I think there might be an option to write a complex single entry that would cover intro and outro jingles and add conditions to only load if there is actual commercials to play. But that's way out my reaches.

i give it a try, stay tuned and thanks for now

[Skydragon980]

i give it a try, stay tuned and thanks for now

this script return an error... what are the type of song for radiodj? i just put my commercial under commercial / paid commercial tag

[Jhonny]

`song_type`=0

[Skydragon980]

`song_type`=0

nothing... doesn't work... ok i try to examine the script maybe is better...

Code: [Select]

#INSERT TITLE HERE   <-- title of the rotation in my case Commercial Cluster
SELECT `songs`.`ID`, `songs`.`artist` FROM `songs` <-- Select songs and id from where? songs is the sql table where radio dj register all my songs i presume
LEFT JOIN `queuelist` ON ( `songs`.`artist` = `queuelist`.`artist`) <-- create a playlist  based on the search you did before
WHERE `song_type`=0 <-- where song type is 0 (but what are the type of songs for radiodj?
AND `enabled`=1 <- obviously the song need to be enabled
AND `count_played`<11 <- and it's count must be 10 or less
AND  `queuelist`.`artist` IS NULL <-- the artist must be null 'cause i can't have repeated artist on my playlist
ORDER BY `date_played` ASC <-- order my playlist by least played first (what happen if all commercials are new?)
LIMIT 5; <-- limit 5 entry for playlist so the listeners are not bored

is that right?

[Filip83]

Hope you are not using the "<--- and comment" in the actual code.
Note that I wrote: WHERE `song_type`=4

Song type 0 would be a song while 4 is a commercial. This number represents what category are we looking at. These numbers could be different in your case. You need to look at the actual table in SQL to confirm the id of the category you want to use.

I made this a while ago but I think it did work for me.

[Jhonny]

Found no errors in the qwerty.



I have imported the tracks as:

Category = Commercials

Track Type = Commercial

[Skydragon980]

Ok the script finally work but now i have another problem i just tried creating an event that do those things in order:

1. Clear Playlist (hoping it clear everything in queue to be played)
2. Load Jingle Start Cluster
3. Load Rotation Commercial Cluster (the one with SQl string)
4. Load Jingle End Cluster
5. Load Normal Rotation
but it load only the 2 jingles and no commercials... where is the error?

[Filip83]

Well now that I look at it again I noticed it actually can't be done.

Unless someone knows a way to pull single tracks to the playlist using SQL.
Using rotations for this task is not appropriate. but you can still load a set number of commercials to a block (using "load track from category"). You can even use repeat rules. Unfortunately there is no way to make sure a commercial won't play for more than "set" times. But it's the closest you can get.

This would be a great addition to RDJ tho. Hope Marius sees this and adds this function in the future.
The ability to add tracks from SQL using events. That would be awesome.