least times played with SQL?

[djdensma]

how can i apply least times played or least recently played with sql rotation?
I like to use SQL rotation for event music show but same tracks repeats each time.

is there a way around this?

[Valdis]

It is all about using the right columns in ORDER BY clause, but there might be other things missing from your query.
Please post the SQL query you are using.

[Filip83]

Did you see: http://www.radiodj.ro/community/index.php?topic=6658.0

To not add same artist in the playlist:

Code Select Expand

AND  `queuelist`.`artist` IS NULL

Least recently played:

Code Select Expand

ORDER BY `date_played` ASC

Least times played:

Code Select Expand

ORDER BY `count_played` ASC

you might also want to enforce artist and track repeat rules:

Code Select Expand

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$))

But like Valdis said...

[djdensma]

thanks guys.. i will test out recomended order by.. no i didnt sort it that way, will retest and report
also wondering if possible add TTS to event that can read special .xml file only during that event. next event to stop it

thanks

[Jhonny]

also wondering if possible add TTS to event that can read special .xml file only during that event. next event to stop it

Yes it can using a trick, with a batch (.bat) file and a small program .bat to .exe for use in a event .

[Jhonny]

Had to look for it.

In http://dj-jhonny.nl/oldtuts/?dir=In%20pdf

Look for dj-jhonny.nl-TTS Plugin.pdf

[djdensma]

this works. what about if i want to sort by random? how do i sort that?

how can i pull top ten play for particular month?. let say top ten for July 2016

thanks

[Valdis]

what about if i want to sort by random? how do i sort that?

Code Select Expand


ORDER BY RAND()


how can i pull top ten play for particular month?. let say top ten for July 2016

You can get top whatever played only if history table contains data for the time period you need. The query will be relatively slow, because history table does not contain reference to track ID and you have to JOIN on artist and title columns to get song IDs.

This query will get top ten tracks for July:

Code Select Expand


-- Select Top10 tracks from July 2016
SELECT songs.ID, songs.artist, songs.title, COUNT(*) AS spins, SUM(history.listeners) as total_listeners
FROM `songs`
LEFT JOIN `history` ON (songs.title = history.title AND songs.artist = history.artist)

-- This is the date range filter
WHERE history.date_played BETWEEN '2016-07-01' AND '2016-08-01'

AND songs.song_type = 0

GROUP BY history.title, history.artist
ORDER BY spins DESC
LIMIT 10;


There is one issue with this query - it is not possible to include data for tracks that have been deleted from songs table, even if the data is present in history table. That's also caused by lack of track ID in history table.

[Filip83]

This might be another one for the examples thread.

[Jhonny]

This might be another one for the examples thread.

Em storing them to.
Never know when i will just put them in RadioDj.

[Valdis]

My HeidiSQL snippets folder contains about a dozen SQL scripts named starting with "RadioDJ". That's a good place to keep them for quick access.

[Marius]

There is one issue with this query - it is not possible to include data for tracks that have been deleted from songs table, even if the data is present in history table. That's also caused by lack of track ID in history table.

Since the history update procedure is not hard coded in the program , it not that complicated to create a new history column for trackID and modify the procedure to insert it there. If this is really needed. 

Create field for track ID:

Code Select Expand

ALTER TABLE `history` ADD COLUMN `trackID` INT NOT NULL AFTER `ID`;

Updated procedure:

Code Select Expand


DROP PROCEDURE IF EXISTS `UpdateTracks`;

DELIMITER $$

CREATE PROCEDURE UpdateTracks(IN trackID INT, IN tType INT, IN curListeners INT, IN historyDays INT, IN pWeight DOUBLE)
BEGIN

SET @tArtist = (SELECT artist FROM songs WHERE ID=trackID);

-- Update Count Played [MODIFIED]
UPDATE `songs` SET `count_played`=`count_played`+1, `date_played`=NOW() WHERE `ID`=trackID;

-- UPDATE ARTISTS
IF tType = 0 OR tType = 9 THEN
    UPDATE `songs` SET `artist_played`=NOW() WHERE `artist`=@tArtist;
END IF;

-- UPDATE REQUESTS
IF tType = 9 THEN
    UPDATE `requests` SET `played`=1 WHERE `songID`=trackID;
END IF;

-- DISABLE BY PLAYCOUNT
UPDATE `songs` SET `enabled`=0, `play_limit`=0 WHERE `enabled`=1 AND `play_limit`>0 AND `count_played`>=`play_limit` AND `limit_action`=1;

-- DELETE BY PLAYCOUNT
DELETE FROM `songs` WHERE `play_limit`>0 AND `count_played`>=`play_limit` AND `limit_action`=2;

-- UPDATE WEIGHT
IF pWeight>0 THEN
    UPDATE `songs` SET `weight`=`weight`-pWeight WHERE `ID`=trackID AND (`weight`-pWeight)>=0;
END IF;

-- UPDATE HISTORY
IF historyDays > 0 THEN
    INSERT INTO `history`(trackID, date_played, song_type, id_subcat, id_genre, duration, artist, original_artist, title, album, composer, `year`, track_no, disc_no, publisher, copyright, isrc, listeners)
    SELECT ID, NOW(), song_type, id_subcat, id_genre, duration, artist, original_artist, title, album, composer, `year`, track_no, disc_no, publisher, copyright, isrc, curListeners FROM `songs` WHERE ID=trackID;
END IF;

-- DELETE OLDER ENTRIES FROM HISTORY
DELETE FROM `history` WHERE TIMESTAMPDIFF(DAY, `date_played`, NOW()) >= historyDays;
END $$

DELIMITER ;

[Valdis]

Since the history update procedure is not hard coded in the program , it not that complicated to create a new history column for trackID and modify the procedure to insert it there. If this is really needed. 

Yes, but most users are not that good with SQL and it is easy to mess up the procedure by one small typo. It would be better to add the column in default installed table structure.

[Marius]

I agree, but for now this is the situation. The next version has the track ID column in history, but for the current version i can't do much.

[Valdis]

Updated procedure:

Code Select Expand


DROP PROCEDURE IF EXISTS `UpdateTracks`;

DELIMITER $$

CREATE PROCEDURE UpdateTracks(IN trackID INT, IN tType INT, IN curListeners INT, IN historyDays INT, IN pWeight DOUBLE)
BEGIN

SET @tArtist = (SELECT artist FROM songs WHERE ID=trackID);

-- Update Count Played
UPDATE `songs` SET `count_played`=`count_played`+1, `date_played`=NOW() WHERE `ID`=trackID;

-- UPDATE ARTISTS
IF tType = 0 OR tType = 9 THEN
    UPDATE `songs` SET `artist_played`=NOW() WHERE `artist`=@tArtist;
END IF;

-- UPDATE REQUESTS
IF tType = 9 THEN
    UPDATE `requests` SET `played`=1 WHERE `songID`=trackID;
END IF;

-- DISABLE BY PLAYCOUNT
UPDATE `songs` SET `enabled`=0, `play_limit`=0 WHERE `enabled`=1 AND `play_limit`>0 AND `count_played`>=`play_limit` AND `limit_action`=1;

-- DELETE BY PLAYCOUNT
DELETE FROM `songs` WHERE `play_limit`>0 AND `count_played`>=`play_limit` AND `limit_action`=2;

-- UPDATE WEIGHT
IF pWeight>0 THEN
    UPDATE `songs` SET `weight`=`weight`-pWeight WHERE `ID`=trackID AND (`weight`-pWeight)>=0;
END IF;

-- UPDATE HISTORY
IF historyDays > 0 THEN
    INSERT INTO `history`(date_played, trackID, song_type, id_subcat, id_genre, duration, artist, original_artist, title, album, composer, `year`, track_no, disc_no, publisher, copyright, isrc, listeners)
    SELECT NOW(), trackID, song_type, id_subcat, id_genre, duration, artist, original_artist, title, album, composer, `year`, track_no, disc_no, publisher, copyright, isrc, curListeners FROM `songs` WHERE ID=trackID;
END IF;

-- DELETE OLDER ENTRIES FROM HISTORY
DELETE FROM `history` WHERE TIMESTAMPDIFF(DAY, `date_played`, NOW()) >= historyDays;
END $$

DELIMITER ;

An there it is, the typo I'm talking about. I get following error:

Code Select Expand

SQL Error (1054): Unknown column 'trackID' in 'field list'

That should be `ID` instead of `trackID` in the last SELECT query.


Update: If anyone is going that route and adding trackID to history table and the procedure, they should run a query to synchronise the new trackID from songs table:

Code Select Expand


UPDATE `history`
INNER JOIN songs ON (history.title = songs.title AND  history.artist = songs.artist AND history.album = songs.album)
SET history.trackID = songs.ID
WHERE history.trackID = 0;

Not 100% perfect but at least the trackID values will point to existing tracks rather than be a useless zero value.