Query Syntax

Holistics Query Syntax gives you more flexibility in designing your report queries. On top of the base SQL syntax supported by your database, Holistics's syntax adds a few convenient functionalities, which are:

info

The examples shown here are based on Postgres SQL or BigQuery's StandardSQL syntax.

SQL Query Variables#

Variables can be used in Reports's queries..

How to use#

To add a filter into your report's query, simply refer to the filter's Variable Name using double curly brackets:

{{ variable_name }}

The variable can be inserted in many different places in the queries. Values passed to the variable are wrapped in single quote to turn them into strings.

Use "noquote" tag for raw value#

In some cases, you may want to use the raw values of the filter instead of their string representation. To do so, simply a |noquote tag after the variable name:

{{ variable_name|noquote }}

Example 1: In a report called "Users with Minimum X Bookings" that uses a filter "Minimum Bookings" with number values, we would want to use the raw min_bookings number instead of a string:

SELECT * FROM users
WHERE [[ num_bookings >= {{min_bookings|noquote}} ]]

Example 2: Here we want to only find products starting with the word “glass-”. The end of the product name can be inserted to the final string with a filter variable:

SELECT
id as "ID",
product_name as "Name"
FROM products
WHERE [[ product_type = 'glass-{{name|noquote}}' ]]

When you select “jar” in the Name filter, the final string will be 'glass-jar', while without the noquote tag, the result would be 'glass-'jar'', which is invalid.

Where to use filter variable#

In WHERE clause#

If a variable is used in a WHERE clause, double square brackets [[ ]] is also needed around the entire condition to ensure even when there is no value passed to the variable, the final query is still valid:

WHERE [[ U.city_name in ( {{city}} ) ]]

For example, if you select 'Tokyo' and 'Manila' in the City dropdown filter, the query above will be translated to:

WHERE U.city_name in ('Tokyo', 'Manila')

If you do not select any value for City variable, the query will be translated to:

WHERE (1 = 1)

The variable can be used to modify a string:

'%' || {{name}} || '%'

Because filter values are, by default, of string type, it can be concatenated with other strings.

CONCAT('%', LOWER({{name}}), '%')

For example, you have a Name filter and selected Bob as value. The function above will return '%bob%', which is can be used in a LIKE statement:

[[ LOWER(U.realname) LIKE CONCAT('%', LOWER({{name}}), '%') ]]

Let's take a look at more complicated condition clauses:

SELECT
id, name, email, country_name, num_bookings
FROM users U
WHERE [[ U.created_at::date >= {{signup_from}} ]]
AND [[ U.created_at::date <= {{signup_to}} ]]
AND [[ U.country_code IN ( {{country}} ) ]]
AND [[ U.name ILIKE '%' || {{name}} || '%' ]]
AND [[ U.email ILIKE '%' || {{email}} || '%' ]]
AND [[ U.num_bookings = {{num_bookings|noquote}} ]]

If the user selected filter values for "Sign Up From", "Sign Up To", and "Country", and leave Name, Email and Booking Number blank, this is the resulted query:

SELECT
id, name, email, country_name, num_bookings
FROM users U
WHERE U.created_at::date >= '2016-03-01'
AND U.created_at::date <= '2016-03-31'
AND U.country_code IN ( 'sg', 'vn' )
AND 1 = 1
AND 1 = 1
AND 1 = 1

Notes

  • If no value is selected for the filter, the condition will be translated to (1 = 1)
  • If the filter allows multi-select, the values will be inserted into the query as a comma-separated list. For example, {{country}} will become 'sg','vn'. You will need to wrap the variable in round brackets and use IN operator like this: country IN ( {{country}} )

In other parts of the query#

Variable and noquote tag can be used to pass reserved keywords to the query. For example, we have a "Time Period" filter with the following values:

The keywords like month, quarter, year must be passed without quote into the query, so we use noquote tag:

SELECT
date_trunc(deal_month, {{aggregation_period|noquote}}) as deal_time
, FROM(deal_value) as total_deal_value
from sales.deals
group by 1

If we select "Monthly" option, the query will be:

SELECT
date_trunc(deal_month, month) as deal_time
, FROM(deal_value) as total_deal_value
from sales.deals
group by 1

SQL Query Conditions#

Basic syntax#

The Query Conditions syntax allows you to use filters to conditionally change your query.

The basic syntax includes:

  • An opening if tag: {{#if ... }} followed by a SQL or a text block
  • An optional else tag: {{#else}} followed by a SQL or a text block
  • The end tag: {{#endif}}

The query can have the following forms:

{{#if variable == 'value1'}}
SQL/TEXT BLOCK 1
{{#else}}
SQL/TEXT BLOCK 2
{{#endif}}
---
{{#if variable1 == 'value1'}}
{{#if variable2 == 'value2'}}
SQL/TEXT BLOCK 1
{{#else}}
SQL/TEXT BLOCK 2
{{#endif}}
{{#endif}}

In other words, you can use the syntax to conditionally run a totally new SQL, or just alter a part of your original SQL query.

Operators#

In the opening {{#if ... }} tag, generic boolean operators are supported including: and, or, ==, !=, not.

For example:

{{#if source == 'direct'}} select * from direct_users {{#endif}}

The conditional statement can be even more complex with and, or and not:

{{#if source == 'direct' and partner != 'youtube'}} VALUE {{#endif}}
{{#if report_type == '270co' or report_type == '268co'}} VALUE {{#endif}}
{{#if source == 'direct' and partner != 'youtube'}} VALUE {{#endif}}
{{#if not(source == 'direct' and partner == 'youtube')}} VALUE {{#endif}}

Use Cases#

Conditional branching is a must-have in the most common programming languages, but unfortunately, it is not available in base SQL.

As shared above, you can now conditionally run a totally new SQL, or just alter a part of your original SQL query. This gives Holistics users even more power to control and be creative with their report logic.

Example 1#

In the following example, we use the condition syntax to add a UNION to the base query if the Chanel filter has value 'youtube':

with R as (
select date_d, sum(cnt) as video_starts
from video_plays
where [[ source = {{source}} ]] and [[ partner = {{partner}} ]]
group by 1 order by 1 desc
{{#if channel == 'youtube'}}
union
select date_d, sum(cnt) as video_starts
from video_plays_youtube
where [[ source = {{source}} ]] and [[ partner = {{partner}} ]]
group by 1 order by 1 desc
{{#endif}}
)
select date_d as "Date", sum(video_starts) as "Video Starts"
from R
group by 1

Example 2#

Here we can conditionally modify the SELECT statement itself:

SELECT
{{#if active = 1}}
last_online_date as "Date",
club_name as "Club",
'Active' as "Type"
{{#else}}
sign_up_date as "Date",
'No club' as "Club",
'Inactive' as "Type"
{{#endif}

This is equal to using three CASE ... WHEN... statements for the three columns:

select
case
when active = 1 then last_online_date
else sign_up_date
end as "Date",
case
when active = 1 then club_name
else 'No club'
end as "Club",
case
when active = 1 then 'Active'
else 'Inactive'
end as "Type"

Query Templates#

Query Templates are SQL snippets that can be applied and reused across different reports. For more details please refer to this page: SQL Query Templates.

The basic syntax to refer to a query template is {{ @template_name(template_var) }} where template_var is the name of the variable used in the template.

For example, we have the following snippet, and the syntax to refer to it in a query is:

{{ @job_status('a_column_name') }}

The column can vary in different querying situation.

In a certain scenario, the full query can be:

SELECT
J.id as "ID",
J.title as "Title"
{{ @job_status('J.status') }} AS "Status"
FROM jobs J