Erland's Plugins and play count based playlists

[netchord]

So, I'm trying to accomplish something in LMS that is extremely straightforward in iTunes and most other PC media managers: build a smart playlist based on a predefined set of criteria, in this case genre & play count. these are not "more like this" or Genius style playlists, where the playlist is derived from a seed track or artist. in this case, I would like to have something like:

"all tracks where Genre = Jazz AND Play Count is less than 2"

I've installed TrackStat and Dynamic Playlist, but do not see any obvious way to accomplish this. any help would be greatly appreciated.

[erland]

You will need SQL Playlist plugin also to accomplish something like this.
At the moment I don't think there is a template for play counts, but I can add that later this week and publish a new SQL Playlist version.

If you want to try it before then, you can create a customized version with raw SQL as follows:
1. Goto Extras/SQL Playlist menu and select to create a new playlist
2. Select the "Advanced" playlist type and change:
- Name to something you want
- Check all genres you want to be included
- Select the "Customize SQL" option and click "Next" button
3. You should now see SQL code similar to this:

-- PlaylistName:My nice playlist
-- PlaylistGroups:
select tracks.url from tracks
	join genre_track on
		tracks.id=genre_track.track
	join genres on
		genre_track.genre=genres.id
	left join dynamicplaylist_history on
		tracks.id=dynamicplaylist_history.id
	where
		audio=1
		and dynamicplaylist_history.id is null
		and genres.name in ('Pop','Rock')
	group by tracks.id
	order by rand()
	limit 10;

4. Now add the following sections to the SQL:

	join track_statistics on
		tracks.url=track_statistics.url

and this (for only including tracks with play count >= 5)

		and track_statistics.playCount>=5

So you end up with something like:

-- PlaylistName:My nice playlist
-- PlaylistGroups:
select tracks.url from tracks
	join track_statistics on
		tracks.url=track_statistics.url
	join genre_track on
		tracks.id=genre_track.track
	join genres on
		genre_track.genre=genres.id
	left join dynamicplaylist_history on
		tracks.id=dynamicplaylist_history.id
	where
		audio=1
		and dynamicplaylist_history.id is null
		and track_statistics.playCount>=5
		and genres.name in ('Pop','Rock')
	group by tracks.id
	order by rand()
	limit 10;

Which results in that you get a smart playlist called "My nice playlist" which includes tracks in either "Pop" or "Rock" genre which have been played at least 5 times.
Theoretically you can do the SQL also without dependency to TrackStat (track_statistics table), but the advantage of TrackStat is that it doesn't increase play counts if you skip a track immediately after it starts, so it's probably preferred to use TrackStat. If you want to do it without TrackStat, let me know and I can post the SQL for that one too.

I'll publish a new SQL Playlist version later this week where you don't have to choose the "Customize SQL" option and edit the SQL manually, but until then you can try to above and check if it works like you want.

[netchord]

Thanks Erland- i'll take a look- my preference is to do this with as few resources as possible, and without having to edit SQL by hand. would appreciate the template you mentioned, but for now it looks like i can uninstall Trackstat and DynamicPlaylist, since the latter is not necessary, and the additional functionality TS provides is not really needed in my case.

[erland]

Thanks Erland- i'll take a look- my preference is to do this with as few resources as possible, and without having to edit SQL by hand. would appreciate the template you mentioned, but for now it looks like i can uninstall Trackstat and DynamicPlaylist, since the latter is not necessary, and the additional functionality TS provides is not really needed in my case.

You will need DynamicPlaylist because SQL PLaylist requires it to work.

If you want to use it without TrackStat, point 4 in my instruction would be something like:

	join tracks_persistent on
		tracks.url=tracks_persistent.url

and this (for only including tracks with play count >= 5)

		and tracks_persistent.playCount>=5

So you end up with something like:

-- PlaylistName:My nice playlist
-- PlaylistGroups:
select tracks.url from tracks
	join tracks_persistent on
		tracks.url=tracks_persistent.url
	join genre_track on
		tracks.id=genre_track.track
	join genres on
		genre_track.genre=genres.id
	left join dynamicplaylist_history on
		tracks.id=dynamicplaylist_history.id
	where
		audio=1
		and dynamicplaylist_history.id is null
		and tracks_persistent.playCount>=5
		and genres.name in ('Pop','Rock')
	group by tracks.id
	order by rand()
	limit 10;

I think any template I make will be using TrackStat as I think LMS standard play counts are broken because they are increased even if you skip the track immediately.
But it's possible for you to create your own template based on it if you like a more user friendly GUI later without having to edit SQL manually every time you want to use a different selection of genres or minimum play count values.

[netchord]

ok, tried your example in your first post above, and received the following error:

Error:
near "join": syntax error
Carp::Clan::__ANON__(): DBI Exception: DBD::SQLite::db prepare failed: near "join": syntax error [for Statement "join track_statistics on tracks.url=track_statistics.url and track_statistics.playCount>=2"] at /Library/PreferencePanes/Squeezebox.prefPane/Contents/server/Slim/Schema/Storage.pm line 126

here's the complete SQL:

-- PlaylistName:SQL Jazz
-- PlaylistGroups:
select tracks.url from tracks
join genre_track on
tracks.id=genre_track.track
join genres on
genre_track.genre=genres.id
left join dynamicplaylist_history on
tracks.id=dynamicplaylist_history.id and dynamicplaylist_history.client='PlaylistPlayer'
where
audio=1
and dynamicplaylist_history.id is null
and genres.name in ('Jazz')
group by tracks.id
order by random()
limit 10;
join track_statistics on
tracks.url=track_statistics.url
and track_statistics.playCount>=2

[erland]

You can't put the extra statements at the end, they need to be inserted in the right places, so you need to put them as I specified in the sample, in your case it would be:

-- PlaylistName:SQL Jazz
-- PlaylistGroups:
select tracks.url from tracks
	join track_statistics on
		tracks.url=track_statistics.url
	join genre_track on
		tracks.id=genre_track.track
	join genres on
		genre_track.genre=genres.id
	left join dynamicplaylist_history on
		tracks.id=dynamicplaylist_history.id and dynamicplaylist_history.client='PlaylistPlayer'
	where
		audio=1
		and dynamicplaylist_history.id is null
		and track_statistics.playCount>=2
		and genres.name in ('Jazz')
	group by tracks.id
	order by random()
	limit 10;

[netchord]

ok, that seems to work. and the "limit 10" in the last line specifies the number of tracks in the playlist? will it update dynamically to include additional tracks if i let it play? any harm in increasing the number of tracks?

[erland]

ok, that seems to work. and the "limit 10" in the last line specifies the number of tracks in the playlist? will it update dynamically to include additional tracks if i let it play? any harm in increasing the number of tracks?

It's just how many tracks it adds each time, the Dynamic Playlist plugin will make sure it requests more tracks when needed.
You can increase it if you like to, but since LMS generally works bad with large playlists I wouldn't recommend to increase it a lot, but I would suggest that you try it a bit first and see if you really feel that it needs to be increased. I think the performance might be affected if you increase it to 100 or something similar.

[netchord]

thanks- will test it out. adding these 3 plugins (TS, DP, SQL) seems to slow down my server significantly, and i hear a fair amount of activity on the external drive (Drobo) where my music is stored. is this to be expected?

[netchord]

so, this worked for a while, until it didn't, and suddenly most of my playlists, SQL or otherwise (created in iTunes) disappeared. full rescan, didn't comeback. uninstalled Dynamic, SQL playlists, and trackstat, rescan, all is back to normal.