How can I create a filter or SQL WHERE clause that a user can conditionally apply? (Community)

View the original community article here

Last tested: Aug 16, 2019
 

If your are asking how you can code an SQL WHERE clause that a user can conditionally apply, we can do this with a combination of a yesno parameter and an sql_always_where on the explore.

The use case is that it is a complex filter and you don't want the user to need to use custom filter syntax.

Example: In this case, you want to have a filter that looks for aircraft model names that contain the manufacturer name. If the user selects 'yes' the filter will be applied to the WHERE clause. If they select 'no' or don't select the filter, the WHERE clause will have 1=1 (always true).

parameter: turn_on_filter{

type: yesno

}

explore: aircraft_models {

sql_always_where: {% if aircraft_models.turn_on_filter._parameter_value == "true" %} ${model} LIKE '%'||${manufacturer}||'%'

{% else %} 1=1

{% endif %};;

}