Applying filters in your plugin query endpoint implementation

A set of filters is sent along with the /query. These filters correspond to the WHERE clause of an SQL query. Applying them to the data is fully optional for a basic plugin and mandatory for a pushdown plugin.

In this example code on Github the 'generateFilters' function transforms filters from the Luzmo format to the filter format of your database. So, it simply looks over the filters and delegates the work to the generateOneFilter function.

Below is a simplified version of the generateOneFilter function, which does the actual work (the complete implementation is available on Github). The implementation is rather simple since the expressions ('<', '<=', ...) do not need translations to SQL.

generateOneFilter( filter, schema ) {
  if ( filter.expression === 'in' || filter.expression === 'not in' ) {
    const values = this.parseExpressionValues( filter.value, columnInfo )
    return `${filter.column_id} ${filter.expression} (${values.join( ',' )}) `
  }
  else {
    const values = this.parseExpressionValues( filter.value, columnInfo )
    if ( !util.isEmpty( values )) {
      return `${filter.column_id} ${filter.expression} ${values[ 0 ]}`
    } else {
      return `${filter.column_id} ${filter.expression}`
    }
  }
}

Lines 4 and 8 highlight that the values of a filter might need to be parsed in order to translate them from the incoming Luzmo format to your database format. Typically, values of the datetime type require extra attention (Luzmo will send dates in the iso format, e.g. 1990-02-28T15:30:19.000Z).

To demonstrate, you'll find an example implementation below that converts a filter's date value to a Postgres value. Since Postgres also accepts iso date strings, it requires almost no work except for proper quoting of the string. However, some databases might only accept Unix timestamps, in which case the value does need conversion.

parseExpressionValues( values, columnInfo ) {
    if ( values ) {
      if ( columnInfo && columnInfo.type === 'datetime' ) {
        return values.map(( val ) => this.encodeDateValue( val, columnInfo.db_type ))
      } 
       ...
    }
    return values
  }


  // Dummy implementation
  encodeDateValue( incValue, dbType ) {
    if ( !util.isEmpty( incValue )) {
      return `'${incValue}'`
    } else {
      return null
    }
  }

The code of the filter parser successfully transforms a Luzmo filter to the SQL filter statement:

NULL Values

In the complete implementation of the generateOneFilter function you will notice certain filter expressions which use 'in' or 'not in' need a little extra attention. This is because Luzmo treats NULL values differently than most databases.

This means that when translating a filter of for example in('a', null), you will have to construct the following query:

WHERE (column IN ('a') OR column IS NULL)

The same appllies for the not in filter. But here we have two separate cases:

If we want to query for all elements not in('a'), we will need to include NULL values in our result.

WHERE (column NOT IN ('a') OR column IS NULL)

if we want to query for elements not in('a', null), we need to leave out NULL values.

WHERE (column NOT IN ('a') AND column IS NOT NULL)