Viernes 30 Jun 2006, 8:22

I was playing around with the SQL Playlist plugin for SlimServer and managed to come up with a few neat lists which are useful for me.

First of all, if you don't already have them:
TrackStat
SQL Playlist
Dynamic Playlist
(at the time I'm writing this, the site is temporarily down)

These are not random playlists. They are supposed to select entire albums. If you try any of these and run into problems (and you do have the above plugins, and you use MySQL as database ), please tell me!

Please excuse the non-existing indentation.

This will select the X (1-10) "latest" (it looks at the file last modified information) MP3 albums which you have not yet listened to at all (meaning: if you have listened to one of the songs on the album, it assumes you have listened to all of it).

– PlaylistParameter1:list:Select number of albums:1:1,2:2,3:3,4:4,5:5,6:6,7:7,8:8,9:9,10:10
SELECT selectedtracks.url
FROM tracks AS selectedtracks
JOIN (
SELECT allalbums.id AS albumid
FROM albums AS allalbums
JOIN (
SELECT alltracks.album
FROM tracks AS alltracks
JOIN track_statistics AS stats
ON stats.url=alltracks.url
WHERE alltracks.content_type='mp3'
GROUP BY alltracks.album
HAVING COUNT(stats.playCount)=0
ORDER BY alltracks.timestamp DESC)
AS selectedalbum_ids
ON selectedalbum_ids.album = allalbums.id
LIMIT 'PlaylistParameter1')
AS selectedalbums
ON selectedalbums.albumid = selectedtracks.album
LEFT JOIN dynamicplaylist_history AS history
ON history.url=selectedtracks.url
WHERE history.id IS NULL

Note: As the dynamic playlist only adds a certain number of files at once, if your album contains more tracks than that number, all tracks on the album won't be played. Perhaps it's possible to fix that - I haven't really tried yet.

Almost the same as the one above, except that all tracks must be played in order for the album to count as played. This "fixes" the above mentioned problem.

– PlaylistParameter1:list:Select number of albums:1:1,2:2,3:3,4:4,5:5,6:6,7:7,8:8,9:9,10:10
SELECT selectedtracks.url
FROM tracks AS selectedtracks
JOIN (
SELECT allalbums.id AS albumid
FROM albums AS allalbums
JOIN (
SELECT alltracks.album
FROM tracks AS alltracks
JOIN track_statistics AS stats
ON stats.url=alltracks.url
WHERE alltracks.content_type='mp3'
AND stats.playCount IS NULL
GROUP BY alltracks.album
ORDER BY alltracks.timestamp DESC)
AS selectedalbum_ids
ON selectedalbum_ids.album = allalbums.id
LIMIT 'PlaylistParameter1')
AS selectedalbums
ON selectedalbums.albumid = selectedtracks.album
LEFT JOIN dynamicplaylist_history AS history
ON history.url=selectedtracks.url
WHERE history.id IS NULL

Note 1: Yes, the ability to select the number of albums is kinda useless in these two cases.
Note 2: If you don't want to limit this to MP3 files only, remove the "alltracks.content_type='mp3'" conditional.

This lets you play FLAC albums with files modified during a selectable amount of time. I guess you can think of it as a "play all" version of a much more advanced New Music list. You can choose numbers 1-12, 15 and 45, and then which "unit" you want the number to be (days, months, years).

– PlaylistParameter1:list:Select number:1:1,2:2,3:3,4:4,5:5,6:6,7:7,8:8,9:9,10:10,11:11,12:12,15:15,45:45
– PlaylistParameter2:list:Select unit:DAY:day(s),MONTH:month(s),YEAR:year(s)
SELECT selectedtracks.url
FROM tracks AS selectedtracks
JOIN (
SELECT allalbums.id AS albumid
FROM albums AS allalbums
JOIN (
SELECT alltracks.album
FROM tracks AS alltracks
WHERE alltracks.content_type='flc'
AND UNIX_TIMESTAMP(DATE_SUB(CURDATE(), INTERVAL 'PlaylistParameter1' 'PlaylistParameter2')) <= alltracks.timestamp
GROUP BY alltracks.album
ORDER BY alltracks.timestamp DESC)
AS selectedalbum_ids
ON selectedalbum_ids.album = allalbums.id)
AS selectedalbums
ON selectedalbums.albumid = selectedtracks.album
LEFT JOIN dynamicplaylist_history AS history
ON history.url=selectedtracks.url
WHERE history.id IS NULL

Note 1: If you don't want to filter on FLAC albums, remove "alltracks.content_type='flc'".

For all of the lists above, where I have filtered on content_type, this won't work perfectly unless you know that all tracks in your albums are in the same format. This isn't a problem if your music collection is in good order, but if you're the kind of person who downloads random junk from many different sources and then puts them together as an album, this filter will be useless.

That's all for today. I hope this was of use to someone :-)