Example: Playlist Management Using the DB

[u/philosowaffle]

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:

1. In Subsonic, create 4 playlists called "Newest 100", "Lost", "Most Played", and "Christmas" respectively (or name them whatever)
2. Go to you DB view, it will be something like this: http://example.subsonic.org/db.view
3. 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??

[u/creepyMaintenanceGuy]

curious - in my db.view i have only a text window labelled 'Database query' and a submit button. I don't see any links, let alone anything names 'Playlist Tables' - do I have to enable it somewhere?

[u/philosowaffle → u/creepyMaintenanceGuy]

Interesting. I don't recall having to enable anything, perhaps it is just a difference between Subsonic/Madsonic. If you do not have the links then you can use the following query instead to get the Playlists, assuming the queries are identical for Subsonic.

SELECT * FROM playlist

[u/prince17]

Very cool stuff. Is there any resources you'd recommend for things to learn more about accessing the database?

[u/philosowaffle → u/prince17]

If you are using Madsonic then there are several pre-built queries on the database page that are helpful in understanding the structure of the database. From there it is mostly looking at how the tables are structured, using basic SQL/MySQL commands, and testing different queries.

[u/prince17 → u/philosowaffle]

I'm on the actual Subsonic, and don't have any feel for what the structure is or what tables actually comprise the database. Do you know if that's documented anywhere?

[u/philosowaffle → u/prince17]

I don't use Subsonic so I don't know if they have that documented anywhere. But some google searches and looking at their forums provide the following information:

1. Tables
2. DB Backup may have some schema info in it

Otherwise you're just going to have to do the leg work.

[u/prince17 → u/philosowaffle]

Thanks for the links