Dynamic Playlists 3 - get help with your custom playlists

[AF1]

This thread is dedicated exclusively to helping users who want to create their own (custom) dynamic playlists with their SQLite statements.
Of course, you can also post your working custom dynamic playlists here for others to use or as a source of inspiration. So nobody has to reinvent to wheel.

Before posting I recommend you take a look at the dynamic playlists (incl. context menu playlists) that come with the plugin. They are a good starting point and may help you understand how SQLite works.

And please read the wiki on the DPL playlist format and the FAQ.

Once you have a working SQLite statement, all you need to do is

• open a plain text editor of your choice (e.g. Notepad on Windows or BBEdit on macOS)
• paste the dynamic playlist definition (incl. your SQLite statement)
• save the file with a sql.xml file extension (instead of txt). Example: mycustomplaylistname.sql.xml
• move this file to the folder for custom playlists called "DPL-custom-lists" in the LMS playlists folder (unless you changed its location).

Now your dynamic playlist should be listed in the playlist group you chose/created or in the Not classified group if it doesn't belong to any group.
Very easy.

So the challenging part is figuring out the SQLite statement. And that's what this thread is for.

I can't promise that I'll have time to answer your requests. Fortunately, there are other users who I know will help you as well.

[nyindieguy]

Glad to see this new thread as I am one of those folks who can't code to save my life. My holy grail is a context menu that uses an existing static playlist and allows me to choose a subset of that playlist that chooses multiple genres and/or a year. If anyone wants to tackle that, I would be grateful.

[AF1]

context menu that uses an existing static playlist and allows me to choose a subset of that playlist that chooses multiple genres and/or a year.

With year you'll have to choose a year. Not optional.

-- PlaylistName:Songs - multiple genres & year   (from selected playlist)
-- PlaylistGroups:
-- PlaylistMenuListType:contextmenu
-- PlaylistParameter1:playlist:Select playlist:
-- PlaylistParameter2:multiplegenres:Select genres:
-- PlaylistParameter3:year:Select year:
select distinct playlist_track.track from playlist_track
	join tracks on
		tracks.url = playlist_track.track and playlist_track.playlist='PlaylistParameter1'
	join genre_track on
		genre_track.track = tracks.id and genre_track.genre in ('PlaylistParameter2')
	left join dynamicplaylist_history on
		dynamicplaylist_history.id=tracks.id and dynamicplaylist_history.client='PlaylistPlayer'
	where
		tracks.year='PlaylistParameter3'
		and tracks.audio = 1
		and tracks.secs >= 'PlaylistTrackMinDuration'
		and dynamicplaylist_history.id is null
	group by playlist_track.track
	order by random()
	limit 'PlaylistLimit';

With decades it's optional (select any).

-- PlaylistName:Songs - multiple genres & decade   (from selected playlist)
-- PlaylistGroups:
-- PlaylistMenuListType:contextmenu
-- PlaylistParameter1:playlist:Select playlist:
-- PlaylistParameter2:multiplegenres:Select genres:
-- PlaylistParameter3:customdecadechained:Select decade:select cast(((ifnull(tracks.year,0)/10)*10) as int),case when tracks.year>0 then cast(((tracks.year/10)*10) as int)||'s' else 'Unknown' end from tracks where tracks.audio=1 group by cast(((ifnull(tracks.year,0)/10)*10) as int) order by tracks.year desc
select distinct playlist_track.track from playlist_track
	join tracks on
		tracks.url = playlist_track.track and playlist_track.playlist='PlaylistParameter1'
	join genre_track on
		genre_track.track = tracks.id and genre_track.genre in ('PlaylistParameter2')
	left join dynamicplaylist_history on
		dynamicplaylist_history.id=tracks.id and dynamicplaylist_history.client='PlaylistPlayer'
	where
		tracks.audio = 1
		and tracks.secs >= 'PlaylistTrackMinDuration'
		and dynamicplaylist_history.id is null
		and
			case
				when 'PlaylistParameter3'!=999999999 then tracks.year>='PlaylistParameter3' and tracks.year<('PlaylistParameter3'+10)
				else 1
			end
	group by playlist_track.track
	order by random()
	limit 'PlaylistLimit';

And without year/decade:

-- PlaylistName:Songs - multiple genres   (from selected playlist)
-- PlaylistGroups:
-- PlaylistMenuListType:contextmenu
-- PlaylistParameter1:playlist:Select playlist:
-- PlaylistParameter2:multiplegenres:Select genres:
select distinct playlist_track.track from playlist_track
	join tracks on
		tracks.url = playlist_track.track and playlist_track.playlist='PlaylistParameter1'
	join genre_track on
		genre_track.track = tracks.id and genre_track.genre in ('PlaylistParameter2')
	left join dynamicplaylist_history on
		dynamicplaylist_history.id=tracks.id and dynamicplaylist_history.client='PlaylistPlayer'
	where
		tracks.audio = 1
		and tracks.secs >= 'PlaylistTrackMinDuration'
		and dynamicplaylist_history.id is null
	group by playlist_track.track
	order by random()
	limit 'PlaylistLimit';

[nyindieguy]

You make all this seem easy!

[nyindieguy]

I tried these out and it appears that there is either a flaw in the code or a bug in the plugin.

With the "multiple genres/select decade" playlist, it selected only 9 songs in spite of there being hundreds that would have qualified. All of the 9 songs were from 1960, in spite of having selected that entire decade of the '60s. When I tried a second time, the exact same 8 songs came up.

With the "multiple genres/select year" playlist, the songs seemed like they were correct, though not as shuffled as I might have wanted, but ended after several hours of play, when there should have been enough songs to last all day. The big tipoff was no Beatles included when Rock and Roll was one of the selected genres and 1967 was the selected year.

Any thoughts?

[AF1]

With the "multiple genres/select decade" playlist, it selected only 9 songs in spite of there being hundreds that would have qualified. All of the 9 songs were from 1960, in spite of having selected that entire decade of the '60s. When I tried a second time, the exact same 8 songs came up.

There was a line I forgot to delete. Fixed.

With the "multiple genres/select year" playlist, the songs seemed like they were correct, though not as shuffled as I might have wanted, but ended after several hours of play, when there should have been enough songs to last all day. The big tipoff was no Beatles included when Rock and Roll was one of the selected genres and 1967 was the selected year.

I recreated this as best I could. A static playlist with about 1500 tracks incl. all Beatles tracks I could find.
Then I called the DPL context playlist from above (with year=1967 and the genres of the tracks in the static playlist) - and no problem, 1967, Beatles and all. Reasonably shuffled as well - though if the source is a playlist with albums (compared to your entire library) I would expect groups of tracks from the same album to show up.

P.S. Your posts are easier to read if you don't quote/repost the entire playlist code :-)

[nyindieguy]

There was a line I forgot to delete. Fixed.



I recreated this as best I could. A static playlist with about 1500 tracks incl. all Beatles tracks I could find.
Then I called the DPL context playlist from above (with year=1967 and the genres of the tracks in the static playlist) - and no problem, 1967, Beatles and all. Reasonably shuffled as well - though if the source is a playlist with albums (compared to your entire library) I would expect groups of tracks from the same album to show up.

P.S. Your posts are easier to read if you don't quote/repost the entire playlist code :-)

Sorry about all that quoting. I've removed them so no one gets confused about your revised code. And thanks again. I'll try it out.

[PasTim]

With apologies for being a bit slow. I have a long standing LMS system, buit I don't quite get what the new DPL vs old DPL issues are.

I've read the FAQ and everything else I could find, but I don't really understand what impact the new version will have on the dynamic play lists I already have. These make extensive use of the database entries created by custom scan and multilibrary. My use of LMS depends almost completely on those database tables. I play almost exclusively classical music, and 'works', so I have to use custom scan and browse, and multilibrary. I did look at the Virtual Libraries but I failed to understand how I could get it to work in a similar way to multilibrary. I have several dozen queries that depend on the multilibrary database.

I don't understand what is meant by 'removes ties to other (unsupported) plugins'. I see that it says that it may work with such plugins, but I'm not sure what it means by 'ties'.

As an example dynamic sql (I have about 20), this one selects random classical works (a series of movements/tracks) where one movement has a top 'rating' of 90 or more. Will the new DPL at least initially still work with this? Will it only fail if multilibrary or customscan fail to create the database tables, or is there some deeper 'tie' that could cause problems?

-- PlaylistName:Random Classical Top Rated Works
-- PlaylistGroups:Random
-- PlaylistOption Unlimited:1
create temporary table sqlplaylist_random_work as
select customscan_track_attributes.value as work, tracks.album as album from customscan_track_attributes
join tracks on
customscan_track_attributes.track = tracks.id
left join dynamicplaylist_history on
tracks.id=dynamicplaylist_history.id and dynamicplaylist_history.client='PlaylistPlayer'
join track_statistics on
tracks.url=track_statistics.url
and track_statistics.rating > 89
where
customscan_track_attributes.module='customtag' and
customscan_track_attributes.attr='WORK' and
dynamicplaylist_history.id is null and
audio=1
group by customscan_track_attributes.value, tracks.album
order by random()
limit 10;

select tracks.url from tracks
join customscan_track_attributes on tracks.id = customscan_track_attributes.track
join sqlplaylist_random_work on tracks.album = sqlplaylist_random_work.album
where sqlplaylist_random_work.work = customscan_track_attributes.value and
customscan_track_attributes.module='customtag' and
customscan_track_attributes.attr='WORK' and
audio=1
group by tracks.id
order by tracks.album,tracks.disc,tracks.tracknum;
drop table sqlplaylist_random_work;

[AF1]

I don't understand what is meant by 'removes ties to other (unsupported) plugins'. I see that it says that it may work with such plugins, but I'm not sure what it means by 'ties'.

I removed code in DPL3 that was specifically aimed at unsupported plugins - for all the reasons and with all the consquences (e.g. no support) explained in the FAQ.
But as long as your SQLite code is valid and the tables you need exists/work there's a good chance it might work with DPL3. DPL3 and DPL2 have different names and preference files so you could temporarily uninstall DPL2 and install DPL3 to test it. Just make sure you read the FAQ parts on SQLPlayList too. In your example above -- PlaylistOption Unlimited:1 won't have any effect with DPL3. It's -- PlaylistLimitOption:unlimited (see DPL3 wiki on DPL playlist format).

I did look at the Virtual Libraries but I failed to understand how I could get it to work in a similar way to multilibrary.

Just out of curiosity: Can you post the code of some of your dynamic playlists (preferably in CODE tags) that involve the MultiLibrary plugin? If there's a chance to replace this plugin with native virtual libraries you'd have one less plugin to worry about and make your setup a bit more 'future-proof'.

[PasTim]

I removed code in DPL3 that was specifically aimed at unsupported plugins - for all the reasons and with all the consquences (e.g. no support) explained in the FAQ.
But as long as your SQLite code is valid and the tables you need exists/work there's a good chance it might work with DPL3. DPL3 and DPL2 have different names and preference files so you could temporarily uninstall DPL2 and install DPL3 to test it. Just make sure you read the FAQ parts on SQLPlayList too. In your example above -- PlaylistOption Unlimited:1 won't have any effect with DPL3. It's -- PlaylistLimitOption:unlimited (see DPL3 wiki on DPL playlist format).

I'm struggling with the complexity of the FAQs. There are so many features I've never heard of, and some terms that evade me, but I'll try. It may prove to be too hard (i'm not as young as I once was).

Just out of curiosity: Can you post the code of some of your dynamic playlists (preferably in CODE tags) that involve the MultiLibrary plugin? If there's a chance to replace this plugin with native virtual libraries you'd have one less plugin to worry about and make your setup a bit more 'future-proof'.

I'd forgotten that the playlist I quoted didn't access the multilibrary table (it didn't need to because I only use WORK on classical tracks). Here's one that does.

-- PlaylistName:Random Non-Classical Rated
-- PlaylistGroups:Random
select url from
	(select tracks.url from tracks
		join multilibrary_track on
			tracks.id=multilibrary_track.track and
			multilibrary_track.library=1
		join track_statistics on
			tracks.url=track_statistics.url
			and track_statistics.rating > 79
		left join dynamicplaylist_history on
			tracks.id=dynamicplaylist_history.id and dynamicplaylist_history.client='PlaylistPlayer'
		where
			audio=1
			and dynamicplaylist_history.id is null
		group by tracks.id
		order by random()
		
		limit 20) as rated
	order by random();