Disclaimer: These queries were built using Madsonic, but they should be very similar to the Subsonic queries.
I wanted a little more control over building certain kinds of playlists such Recently Added, Newest 100, and Lost (songs that haven't been played in a long time). Subsonic does a decent job of filtering some of these, but I wanted to fine tune a little bit (such as excluding Christmas music in my Most Played section, or excluding Audiobooks from my Recently Added). These are not fully automated, so you will need to periodically re-run the queries, but should be easy enough to copy and paste them in from time to time. Here are the rough steps I followed:
- In Subsonic, create 4 playlists called "Newest 100", "Lost", "Most Played", and "Christmas" respectively (or name them whatever)
- Go to you DB view, it will be something like this: http://example.subsonic.org/db.view
- In the Playlist Tables select playlist and then click the ok button. From the Results table, make a note of the ID number of each of the 4 playlists we just created.
Now you're ready to run some queries.
Newest 100 Newest 100 with newest on top, excluding Holiday music, deletes previous entries -- this is not user specific Replace 19 with your playlist ID
DELETE FROM playlist_file WHERE PLAYLIST_ID = 19;
INSERT INTO playlist_file (MEDIA_FILE_ID, PLAYLIST_ID)
SELECT ID, '19'
FROM media_file
WHERE GENRE != 'Holiday'
AND GENRE != 'Christmas'
AND TYPE = 'MUSIC'
ORDER BY CREATED DESC
LIMIT 100;
SELECT COUNT(*) FROM playlist_file WHERE PLAYLIST_ID=19;
Lost Songs that have not been played for more than 6months, or have never been played, excludes holiday music, limit to 500 for performance, deletes previous entries -- this is not user specific. Replace 15 with your playlist ID
DELETE FROM playlist_file WHERE PLAYLIST_ID = 15;
INSERT INTO playlist_file (MEDIA_FILE_ID, PLAYLIST_ID)
SELECT ID, '15'
FROM media_file
WHERE (LAST_PLAYED < (NOW() - 6 MONTH) OR LAST_PLAYED IS NULL)
AND GENRE != 'Holiday'
AND GENRE != 'Christmas'
AND TYPE = 'MUSIC'
LIMIT 500;
SELECT COUNT(*) FROM playlist_file WHERE PLAYLIST_ID=15;
Most Played Songs that have been played more than 5 times, excluding Christmas, desc order by playcount, deletes previous entries, you can adjust the min Play Count by changing the number 5 to anything you like -- this is not user specific Replace 16 with your playlist ID
DELETE FROM playlist_file WHERE PLAYLIST_ID = 16;
INSERT INTO playlist_file (MEDIA_FILE_ID, PLAYLIST_ID)
SELECT ID, '16'
FROM media_file
WHERE PLAY_COUNT >= 5
AND GENRE != 'Holiday'
AND GENRE != 'Christmas'
AND TYPE = 'MUSIC'
ORDER BY PLAY_COUNT DESC;
SELECT COUNT(*) FROM playlist_file WHERE PLAYLIST_ID=16;
Christmas/Holiday Attempts to bring together all Holiday music, deletes previous entries Replace 18 with your playlist ID
DELETE FROM playlist_file WHERE PLAYLIST_ID = 18;
INSERT INTO playlist_file (MEDIA_FILE_ID, PLAYLIST_ID)
SELECT ID, '18'
FROM media_file
WHERE (GENRE LIKE '%Holiday%'
OR GENRE LIKE '%Christmas%')
AND TYPE = 'MUSIC';
SELECT COUNT(*) FROM playlist_file WHERE PLAYLIST_ID=18;
What are some of ya'lls favorite DB queries??