CustomNodes and SQL

[sonos]

I currently try to figure out how to modify the CustomNodes:

Code: Select all

var customNodesDefinitions = {

    tracks: [{
            title: 'Show 5 most played tracks',
            description: 'Currently showing 5 most played tracks',
            sql: 'Songs.PlayCounter > 0 ORDER BY Songs.PlayCounter DESC LIMIT 5'
    }, 
    .....
    {

To further specify this query by inserting the e.g. DateAdded element and using the SQL editor from MM5, I was able to successfully extend this query using the SQL editor in MM5.

Code: Select all

SELECT
Songs.PlayCounter, strftime('%Y',julianday(DateAdded + 2415018.5),'localtime') as addedyear
FROM
   Songs
WHERE addedyear = '2015' AND Songs.PlayCounter > 0 ORDER BY Songs.PlayCounter DESC LIMIT 5

However if I insert this into the customNodesDefinitions:

Code: Select all

var customNodesDefinitions = {

    tracks: [{
            title: 'Show 5 most played tracks in 2015',
            description: 'Currently showing 5 most played tracks',
            sql: 'strftime('%Y',julianday(DateAdded + 2415018.5),'localtime') = '2015' AND Songs.PlayCounter > 0 ORDER BY Songs.PlayCounter DESC LIMIT 5'
    }, 
    ....
    {

I always get an error message.
What's wrong in line ?:

Code: Select all

sql: 'strftime('%Y',julianday(DateAdded + 2415018.5),'localtime') = '2015' AND Songs.PlayCounter > 0 ORDER BY Songs.PlayCounter DESC LIMIT 5' 

Every hint is appreciated

Carsten

[drakinite]

I think you just had a syntax error, because you have un-escaped apostrophes inside your string. When editing customNodesDefiniton.js in an IDE like Visual Studio Code, it'll usually show you when there's an error in your JS syntax.

https://i.imgur.com/GaT5qF7.png

Simplest fix is to just replace ' with either " or `. It seems to work for me after fixing the syntax error.

Code: Select all

    {
        title: 'Show 5 most played tracks in 2015',
        description: 'Currently showing 5 most played tracks',
        sql: `strftime('%Y',julianday(DateAdded + 2415018.5),'localtime') = '2015' AND Songs.PlayCounter > 0 ORDER BY Songs.PlayCounter DESC LIMIT 5`
    }

[sonos]

Hi drakinite
Thanks for the hint!
I usually use VS Code IDE, but so far I've only tested the queries in the SQL editor. Copy and Paste is not always advisable.
Thanks again
Carsten