I’m trying to do stats on my playlist, for that I use the playlistlog table or the logging table on my website to have real data (played items != planned ones). I try to know how many real calls are done on a folder in a day/week/month.
Is there a way to know the folder name (or id) where a song was picked when it’s played ?
I workarounded this with a Custom SQL Query in the Logging system.
I use the ‘info’ column of the playlistlog table, I update the field with the folder :
UPDATE playlistlog SET info = ( SELECT f.name FROM folders f LEFT JOIN item_folders itfo ON itfo.folder = f.idx WHERE itfo.item = %U LIMIT 1 )
WHERE starttime BETWEEN NOW() - INTERVAL 30 SECOND AND NOW() AND item = %U;
Then I can have the number of calls for each folder :
SELECT info, COUNT(info) AS appels FROM playlistlog pl
WHERE DATE(starttime) BETWEEN '2021-10-21' AND '2021-10-21'
AND info IS NOT NULL
GROUP BY info;
limitions are :
It’s not really the folder defined in the hour model where is picked songs , but the folder containing the song at the moment it’s played (the case of folder with recursive call on subfolder…)
I hope ‘info’ field is not used by another functonnality ? Or can I add a field to playlistlog structure ?
can’t handle items wich are in multiple folders (LIMIT 1 in subquery), so it’s one of its folders
It’s doing the job for my application…but is it possible to consider the folder information is available in the DB/Playlist view, in the playout system (and the logging) in a future version, please ?
It would be very useful !