Smart Playlists

[deluan]

Smart playlists can be created as a json object into a file with .nsp extension and Navidrome will create a dynamic/query based playlist and keep it updated. These .nsp files will be imported the same way as normal playlists, i.e. at scan time. See comment below for a complete list of fields and operators available.

Some examples:

• Recently Played:

{
  "name": "Recently Played",
  "comment": "Recently played tracks",
  "all": [
    {"inTheLast": {"lastPlayed": 30}}
  ],
  "sort": "lastPlayed",
  "order": "desc",
  "limit": 100
}

• 80's Top Songs

{
  "all": [
    { "any": [
      {"is": {"loved": true}},
      {"gt": {"rating": 3}}
    ]},
    {"inTheRange": {"year": [1981, 1990]}}
  ],
  "sort": "year",
  "order": "desc",
  "limit": 25
}

• Favourites

{
  "all": [
    {"is": {"loved": true}}
  ],
  "sort": "dateLoved",
  "order": "desc",
  "limit": 500
}

• All songs in random order (not recommended for large libraries)

{
  "all": [
    {"gt": {"playCount":-1}}
  ],
  "sort": "random"
}

TODO

• PHASE 1 - On demand

  • Add rules to playlists table
  • Rule compiler (to SQL)
  • Calculate tracks on get
    • Cache results (for 5 secs)
  • Import smart playlists found in Music Folder (extension .NSP)
  • Handle permissions
    • Smart Playlists (tracks) are read-only
    • Cannot be shared
  • Refactor and clean up

• PHASE 2 - Live update

  • Update fields table on smart playlist create/update
  • Live update queue: Persistent queue (SQLite3?) to store id and rules of playlist to process
  • SP queue processor: get id and rules from queue and refresh tracks based on rules
  • UpdateNotifier (in memory)
    • Group updates (10 items or 3 secs idle)
    • Add playlists to live update queue
    • Call it from Media Annotations and Scanner (when new tracks are imported)

• PHASE 3 - UI

  • Rule editor
  • Reorganize sidebar

• MISCELANEOUS

  • Sort by "random"
  • Album and Artist attributes
  • Add a "playlist" field - see how to use it

[deluan]

Current list of fields available (case-insensitive):

title
album
artist
albumartist
hascoverart
tracknumber
discnumber
year
size
compilation
dateadded
datemodified
discsubtitle
comment
lyrics
sorttitle
sortalbum
sortartist
sortalbumartist
albumtype
albumcomment
catalognumber
filepath
filetype
duration
bitrate
bpm
channels
genre
loved
dateloved
lastplayed
playcount
rating

Operators available (case-insensitive):

is
isNot
gt
lt
contains
notContains
startsWith
endsWith
inTheRange
before
after
inTheLast
notInTheLast
inPlaylist
notInPlaylist

[RedKage]

Hello, not sure if I should post this here or make a new issue.
I have a use case which is to find all the songs which are unrated. I want to find those which have no star.

I played a bit with the NSP, and my take is that it's like an expression tree which builds SQL queries. Haven't looked at the source, yet. And indeed, the "problem" is that
select * from annotation where rating = 0 and item_type = 'media_file'
returns only the songs with no rating, but they still need to have a row in the annotation table.

In effect I have only 10 songs returned without star. When in actuality I have thousands, but these have never been annotated.
So they are never in the scope of the expression tree. So they can never be found? Or is there another way?

EDIT: I wonder if there's like some sort of left outer join or something that would still target media_files which have no annotations yet, kinda like changing that LeftJoin in persistence/playlist_repository.go:207

[deluan]

Hello, not sure if I should post this here or make a new issue.

That's fine

I played a bit with the NSP, and my take is that it's like an expression tree which builds SQL queries. Haven't looked at the source, yet. And indeed, the "problem" is that select * from annotation where rating = 0 and item_type = 'media_file' returns only the songs with no rating, but they still need to have a row in the annotation table.

Yes, that's how it works, and yes, this is a "problem". I think a good approach for this is to introduce a new operator, isSet (true/false) that will be converted to is null/is not null and will allow these use cases where you don't have the record in the annotation table.

EDIT: I wonder if there's like some sort of left outer join or something that would still target media_files which have no annotations yet, kinda like changing that LeftJoin in persistence/playlist_repository.go:207

It is already a left join, the issue is that when there's no matching row in the annotation table, its values are all null

[deluan]

I actually did some experimentation and I think a better way is to wrap all fields from the left joined tables with COALESCE, like this:

COALESCE(annotation.rating, 0 ) = 0)

This will allow to use "is": {"rating": 0} in the NSP file.

[0bmay]

First off, thanks for the smart playlists.. It really opens up the possibilities for Navidrome!

using the latest docker image (Navidrome Version: | 0.47.0 (8c7d95c)), trying to use dateadded and datemodified return invalid key errors. I'm attempting to make a Songs newly added and unrated playlist.. I took a cue from the random sorting post on reddit. Not sure if it would work, but it appears I can't filter on dateadded or datemodified. I'm assuming I'm doing something wrong, but just in case....

{
"all": [
{"is": {"rating": 0}},
{"datemodified": {"gt": "DATE('now','-90 day')"}}, -- using this line or the next..
{"dateadded": {"gt": "DATE('now','-90 day')"}}
],
"sort": "dateadded",
"order": "asc",
"limit": 100
}

time="2021-11-20T00:56:54Z" level=error msg="Error parsing SmartPlaylist" error="invalid expression key dateadded" playlist="Songs to Rate - Newly Added" requestId=e31872cd1409/MIUyT3LsdO-001067,
time="2021-11-20T00:56:54Z" level=error msg="Error parsing playlist" error="invalid expression key dateadded" path="/music/playlists/Songs to Rate - Newly Added.nsp" requestId=e31872cd1409/MIUyT3LsdO-001067,
time="2021-11-20T01:02:46Z" level=error msg="Error parsing SmartPlaylist" error="invalid expression key datemodified" playlist="Songs to Rate - Newly Added" requestId=e31872cd1409/MIUyT3LsdO-001108,
time="2021-11-20T01:02:46Z" level=error msg="Error parsing playlist" error="invalid expression key datemodified" path="/music/playlists/Songs to Rate - Newly Added.nsp" requestId=e31872cd1409/MIUyT3LsdO-001108,

[deluan]

Hey @0bmay, the issue is that the key of the rule has to be the operator, not the field. So in the snippet:

{"dateadded": {"gt": "DATE('now','-90 day')"}}

should be:

{"gt": {"dateadded": "DATE('now','-90 day')"}}

Also notice the operator inTheLast, that was created specifically for this type of rules:

{"inTheLast": {"dateadded": "90"}}, 

I don't even know if DATE('now','-90 day') will work..

[0bmay]

Thank you @deluan!