Data source query editor

Create data source queries for table and chart widgets to select specific data from a database. Queries are built in the form of a database field followed by the = sign and an operator, number or string:

database_string=my string
database_number=1234
database_numger=BETWEEN(3, 6)

The queries can be enriched by specific JavaScript code:

Time an date calculation with momentjs

Operators

Operator	Description
`BETWEEN()`	Range from start to end value. Start and end values are comma separated
`IN()`	Comma separated list of numbers or strings to compare against
`GT()`	Greater than value
`LT()`	Lower than value
`NOT()`	Negation of an expression
`AND()`	List of comma separated expressions. All must be fulfilled
`OR()`	List of comma separated expressions. At least one must be fulfilled
`NULL()`	Checks if a value is NULL

Examples

Select all records with a salary greater than 40000:

salary=GT(40000)

Select all records with names other than James, John and Joe:

name=NOT(IN(James%2CJohn%2CJoe))

Select all records wit an non NULL customerId:

customerId=NOT(NULL())

Commas in arguments should be escaped:

. -> %2C

This is needed if nested operators are used as in the example above.

Examples with momentjs

In the following examples, params.interval is set by a date picker widget

Select only records with a date between the begin and end date/time parameter provided by a date picker widget. Begin, end and format() are used from the momentjs library:

date=BETWEEN(${interval(params.interval).begin.format()}, ${interval(params.interval).end.format()})

Select records from one year before:

year=${interval(params.interval).begin.subtract(1, 'years').format('YYYY')}

Query the same month one year before with the month formatted without leading zeroes:

month=${interval(params.interval).begin.subtract(params.sub, 'years').format('M')}

Query the weekday (0-6):

weekday=${interval(params.interval).begin.day()}

Parameter dependent query

If a query should be applied only if a specific paramter is set (e.g. a filter matrix query), the column as well as the query string can be wrapped into if statements:

${filterMatrix.device_name ? 'device_name' : 'none'}=${filterMatrix.device_name ? filterMatrix.device_name : ''}

If the parameter filterMatrix.device_name is defined, the colum will be set to ‘device_name’ and the query string to 'filterMatrix.device_name’):

device_name=filterMatrix.device_name

If the same parameter is not defined, the resulting query will be configured for the probably non-existent column ‘none’:

 none=''