The custom filter values can be used in several more ways in the SQL query to give additional capabilities.
The default usage matches both a column (or a complex expresssion in square brackets) with the selected filter values taking into account the number of selected values.
SELECT * FROM flights WHERE {origin=origin_airports};
would be executed as either of these depending on what the user selects in the filter bar:
SELECT * FROM flights WHERE 1=1; SELECT * FROM flights WHERE origin='LAX'; SELECT * FROM flights WHERE origin IN ('LAX', 'SFO);
There are more ways to tap into the filter values:
- {origin_airports.min} - gives access to the minimum of the selected values or NULL if no value is selected
- {origin_airports.max} - gives maximum of the selected values or NULL if no value is selected
- {origin_airports.count} - gives number of selected values (0, 1 or more)
- {origin_airports.selected} - true or false if the filter has a value selected (TRUE or FALSE)
- {origin_airports.value} - replaced with a value in case of single selected value or NULL in case 0 or more than 1 value is selected
- {origin_airports.values} - replaced with braced list of selected values '(1,2,3)', empty list if no values are selected. This is useful for doing 'IN' WHERE clause in the SQL query
- {origin_airports.values_array} - replaced with square-braced list of selected values '[1,2,3]', empty list if no values are selected. This is useful for databases that allow to work with array literal (e.g. BigQuery)
- {origin_airports.values_concat} - replaced with a string value of all the selected values concatenated with comma (',') as separator. Empty string in case no values are selected.
- {my_filter.value_raw} - replaced directly with the value of the filter without any manipulation - useful for instance if you want to specify dynamically the column to choose in the query
Here is an example of a query using the filter values in this way:
SELECT {origin_airports.min}, {origin_airports.max}, {origin_airports.count}, {origin_airports.selected},
{origin_airports.values_concat},
{my_filter.value_raw}
FROM flights WHERE origin={origin_airports.value} OR origin IN {origin_airports.values}
You can also tap into the currently used timerange or aggregation values in the same way:
SELECT {timerange.value}, {aggregation.value}
or use it directly in the SQL for instance to access pre-aggregated tables by the aggregation:
SELECT * from aggregated_data_by_{aggregation.value_raw}