Metabase has the flexible ability to allow variables in native (SQL) queries. This lets you dynamically replace values in your queries using filter widgets or through the query's URL.
Options and settings for your variables will appear in the Variables side panel of the native query builder once you've defined a variable. So, how do you define a variable?
Typing {% raw %}{{variable_name}}{% endraw %} in your native query creates a variable called variable_name. Variables can be given types in the side panel, which changes their behavior. All variable types other than field filter will cause a filter widget to be placed on this question corresponding to the chosen variable type. When a value is selected via a filter widget, that value replaces the corresponding variable in the SQL template, wherever it appears.
This example defines a variable called cat, allowing you to dynamically change the WHERE clause in this query:
SELECT count(*)
FROM products
WHERE category = {% raw %}{{cat}}{% endraw %}
Giving a variable the Field filter type allows you to connect SQL cards to dashboard filter widgets. A field filter variable inserts SQL similar to that generated by the GUI query builder when adding filters on existing columns. This is useful because it lets you do things like insert dynamic date range filters into your native query. When adding a field filter, you should link that variable to a specific column. Field filter variables should be used inside of a WHERE clause.
Example:
SELECT count(*)
FROM products
WHERE {% raw %}{{created_at}}{% endraw %}
To make an optional clause in your native query, type [[brackets around a {% raw %}{{variable}}{% endraw %}]]. If variable is given a value, then the entire clause is placed into the template. If not, then the entire clause is ignored.
In this example, if no value is given to cat from its filter widget or URL, then the query will just select all the rows from the products table. But if cat does have a value, like Widget, then the query will only grab the products with a category type of Widget:
SELECT count(*)
FROM products
[[WHERE category = {% raw %}{{cat}}{% endraw %}]]
To use multiple optional clauses you must include at least one regular WHERE clause followed by optional clauses, each starting with AND.
Example:
SELECT count(*)
FROM products
WHERE True
[[AND id = {% raw %}{{id}}{% endraw %}]]
[[AND category = {% raw %}{{category}}{% endraw %}]]
If you still have questions, or want to share Metabase tips and tricks, head over to our discussion board. See you there!