Dynamic Playlists

From Musik

Dynamic playlists can be an incredibly flexible tool to do various things: finding songs from specific genres, keeping track of your library changes, monitoring your listening behavior, and many more.

A dynamic playlist consist of a simple description, e.g. "show me all things that I have added to my library today".

Table of contents
1 How To Create a Dynamic Playlist
2 Basic Commands
3 Avaliable Fields to Use
4 Tips
5 Example Queries
6 Forum Discussions
[edit]

How To Create a Dynamic Playlist

See also: musikCube Help

[edit]

Basic Commands

Dynamic playlists are nothing more than simple SQL queries. See SQLite for more information on the technical details.

[edit]

Avaliable Fields to Use

See also: Song Fields in SQLite

[edit]

Tips

  • Use "lower()" when comparing strings (or use "LIKE" which is also case insensitive)
[edit]

Example Queries

These are some of the queries that users have shared in the forum.


Songs that have not been played in the last 2 weeks: 

datetime(lastplayed) < datetime('now', '-14 days')

Not played in last 2 weeks, but played at least once, sorted by times played (more advanced than above): 

timesplayed > 0 AND datetime(lastplayed) < datetime('now', '-14 days') ORDER BY timesplayed DESC LIMIT 50;

All files in C:\mp3\ (useful when sharing a computer with other users, or for laptops with local and remote files): 

filename like 'C:\mp3\%'

All MP3 files in subfolders only in C:\mp3\: 

filename like 'C:\mp3\%\%' AND filename like '%.mp3'

Songs added today: 

date(timeadded) = date('now')

Songs added this month: 

datetime(timeadded) > datetime('now', 'start of month')

Songs with the word 'japanese' in the comments tag: 

notes like '%japanese%'

Random selection of 50 songs that changes each time you look at it: 

1 = 1 ORDER BY random() LIMIT 50

Random selection of 50 songs that have been added last week: 

julianday(timeadded) > (julianday('now') - 7) ORDER BY random() LIMIT 50

Random selection of 50 songs that you have played more than average: 

timesplayed > (SELECT avg(timesplayed) FROM songs WHERE timesplayed > 0) ORDER BY random() LIMIT 50

Random selection of 50 songs that are older than two months: 

julianday(timeadded) < (julianday('now') - 60) ORDER BY random() LIMIT 50

Random selection of 50 songs that you haven't played yet: 

timesplayed = 0 ORDER BY random() LIMIT 50

Random selection of one album with more than one song, it changes each time you choose the list (there is a bug (http://www.musikcube.com/punbb/viewtopic.php?pid=12434#p12434) with the View Boxes): 

album IN (SELECT album FROM songs GROUP BY album HAVING count(*) > 1 ORDER BY random() LIMIT 1)

Random selection of songs rated with more than three stars: 

format != 4 AND rating > 3 ORDER BY random() LIMIT 50

Duplicate songs in your library, based on title/artist: 

lower(title)||lower(artist) IN (SELECT lower(title)||lower(artist) FROM songs
GROUP BY lower(artist),lower(title) HAVING count(*) > 1) ORDER BY artist,title

Duplicate songs in your library, based on title/artist and album: 

lower(title)||lower(artist)||lower(album) IN (SELECT lower(title)||lower(artist)||lower(album) FROM songs 
GROUP BY lower(artist),lower(title),lower(album) HAVING count(*) > 1) ORDER BY artist,title,album

Find genres having 2 or less songs, nice to clean up mess (based on above, thanks!): 

lower(genre) IN (SELECT lower(genre) FROM songs GROUP BY lower(genre) HAVING count(*) <= 2)

Find songs missing a certain tag property (using genre for example): 

genre = ''

All unrated songs: 

rating = 0

Top 50 songs by rating: 

rating > 0 ORDER BY rating DESC LIMIT 50

Songs you don't like and have rated with "-1": 

rating = -1

Songs from artists that have over 10 songs: 

artist in (select artist from songs group by artist having count(*) > 10)

A kind of "AutoDJ" like in wxMusik: 

rating >= 0 and genre != 'Speech' and genre != 'Comedy' and  datetime(lastplayed) < datetime('now', '-5 days') 
ORDER BY random()

Songs longer than 5 minutes: 

duration > (5 * 60 * 1000)

Short songs: 

duration < (1 * 60 * 1000)

Songs in a playlist with the name "My Playlist": 

songid IN (SELECT DISTINCT s.songid FROM std_playlist_song s, std_playlist n
WHERE s.std_playlist_id = n.std_playlist_id AND n.std_playlist_name = 'My Playlist')

Top 100 of the last 4 weeks/1 month (using rating and timesplayed to determine the actual popularity; the +1 is to get the correct sorting for 0-star tracks): 

timesplayed > 0 
AND datetime(lastplayed) > datetime('now', '-1 month') 
ORDER BY timesplayed*(rating+1) DESC
LIMIT 100;

Top 100 soundtrack and score pieces (works similar to above): 

(genre LIKE "Soundtrack" OR genre LIKE "Score")
AND timesplayed > 0 
ORDER BY timesplayed*(rating+1) DESC
LIMIT 100;

Top 100 songs added this month (based on timesplayed and rating like above) 

timesplayed > 0
AND datetime(timeadded) < datetime('now') 
AND datetime(timeadded) > datetime('now', '-1 month') 
ORDER BY timesplayed*(rating+1) DESC 
LIMIT 100;

Display albums with more than one artist only (e.g. compilations): 

album IN (SELECT album FROM (SELECT DISTINCT artist, album FROM songs) GROUP BY album HAVING count(*) > 1)

Filter out net radios: 

filename NOT LIKE 'http%'

30 newest albums, songs sorted after adding time (newest first) and album belonging: 

album IN (SELECT album FROM songs GROUP BY album ORDER BY julianday(timeadded) DESC LIMIT 30)
ORDER BY (select max(julianday(s.timeadded)) FROM songs AS s WHERE s.album = songs.album AND
s.artist = songs.artist) DESC, tracknum
[edit]

Forum Discussions

Retrieved from "http://www.musikcube.com/wiki/Dynamic_Playlists"