WHERE
The WHERE clause helps filter the data based on several filter conditions that can be combined
using boolean operators such as AND, OR and NOT. In SQL Frames the WHERE clause is specified
using the df.fdf() API.
In the following example, data with null values is filtered out.
Notice how the OR clause is constructed procedurally using the array map feature of javascript.
The ability to use the declarative SQL constructs but assemble them procedurally makes SQL Frames quite powerful
once the users get the grasp of the idea.
Filter Condition Operations
SQL Filtering Operators
In addition to boolean operators and, or and not, the following filter operations are provided.
| SQL Operator | SQL Frames API | Example | Comments |
|---|---|---|---|
| = | eq | eq('field-name',value) | |
| = | feq | feq('field1','field2') | |
| > | gt | gt('field-name',value) | |
| < | lt | lt('field-name',value) | |
| ≥ | gte | gte('field-name',value) | |
| ≤ | lte | lte('field-name',value) | |
| in | isin | isin('field-name',[v1,v2,...]) | in is a keyword in JavaScript |
| between | between | between('field',v1,v2) | |
| is null | isnull | isnull('field-name') | |
| is not null | notnull | notnull('field-name)` | |
| like | like | like('field-name','some-string') |
Other Filtering Operators
SQL Frames provides additional APIs that may not be available within SQL. It may not be possible to transpile these API into a valid SQL. If auto generated SQL is a mandatory requirement, do not use these additional API. However, they provide convenience and better low-code experience.
| SQL Frames API | Example | Comments |
|---|---|---|
contains | contains('field-name','some-string') | |
startsWith | startsWith('field-name','some-string') | |
endsWith | endsWith('field-name','some-string') | |
anynulls | anynulls('field1','field2',...) | same as field1 is null OR field2 is null OR .... The API allows passing no field names in which case it is all the fields in the row. |
nonulls | nonulls('field1','field2',...) | same as not(anynulls('field1','field2',...)) which translates to field1 is not null AND field2 is not null ...`. |
SEMI / ANTI JOINs
Semi and Anti JOINs are special joins that result in filtering data based on presence or absence of data in another table. SQL Frames supports these JOINs as well.
SEMI JOIN
Use the SQL.where.exists() API for the SEMI join.
ANTI JOIN
Use the SQL.where.notexists() API for the ANTI join.
Default Where Clause
It may be desirable to set a default where clause that will become effective whenever the filtes are cleared.
This can be done using the fdf.defaultWhere() API.
In the below example, use the filter builder or contextual filters to modify the filter and then use the Clear button in the filter builder or Clear filters in the context menu and notice the filters are reset to the default filter.
Read-only
Sometimes not only you want to reset to default, you may not want the default where clause to be
modified (although it may be augmented with additional clauses). This can be accomlished with the
filter.readOnly() API.
Open the filter builder from the menu and see that the default filter is displayed but is not editable.