Overview

You can extend the power of your SQL queries in many interesting ways by using the open source Liquid template language. Using Liquid, the SQL behind your Mode reports can be manipulated at report run time using loops, if/then statements, and other advanced structures that might be difficult or impossible to do in SQL alone. Several examples of these methods are shown below.

Whenever a query is executed in a Mode report, Liquid code (if present) is evaluated first before the code is sent to your database for execution as SQL. Liquid code is composed of:

Objects contain attributes that are used to render dynamic content into your SQL query at run time. Objects are wrapped in double curly brackets {{...}}.

Filters are simple methods that modify the output of numbers, strings, variables and objects. They are placed inside Object tags {{ }} and denoted with a | character.

Tags make up the programming logic (e.g., if/else, for, etc.) that tells your code what to do. They are wrapped in a single curly bracket and a percent sign {%...%}. Tags don’t themselves produce output that gets rendered into your query, but they may instruct Mode to render, ignore, repeat or otherwise modify specific lines of SQL code.

Full documentation on what’s possible with Liquid is available on the Shopify help site and documentation for the Liquid GitHub repo.

Common techniques

Variables

Use variables in Liquid to make your code more extensible and maintainable. Declare a variable using the assign method. For example:

SELECT * FROM employee_table WHERE favorite_food = '{{ fav_food }}'

{% assign fav_food = 'peaches' %}

The above code would render into the following code for execution against the database:

SELECT * FROM employee_table WHERE favorite_food = 'peaches'

You can refer to a variable as many times as you want across all of the queries within your report.

If/else

Use if/else statements and other control flow tags to change your SQL code dynamically in response to inputs from things like variables or parameters. In the following example, the query that is executed against the database will be different depending on the value of the car_type variable:

{% assign car_type = 'trucks' %}

SELECT *
{% if car_type == 'trucks' %}
  FROM truck_table
{% elsif parameter == 'cars' %}
  FROM car_table
{% endif %}

If car_type = 'trucks', the following code is executed:

SELECT * FROM truck_table

If car_type = 'cars', the following code is executed:

SELECT * FROM car_table

Loops

Loops and other Liquid iteration tags can be used to programmatically generate lists of variables, join statements, columns to select, unions and other things. The query below shows a simple example of a For loop:

SELECT *
  FROM sports_teams

{% for i in (1..4) %}
  LEFT JOIN draft_picks d{{i}}
    ON d{{i}}.team_name = sports_teams.team_name
  AND d{{i}}.round = {{i}}
{% endfor %}

The above code joins the draft_picks table to the teams table four times. Each join is assigned a distinct alias (d1 through d4) and a different condition (the round number of the draft pick). The rendered code that is actually sent to the database for execution is:

SELECT *
  FROM sports_teams
  LEFT JOIN draft_picks d1 ON d1.team_name = sports_teams.team_name AND d1.round = 1
  LEFT JOIN draft_picks d2 ON d2.team_name = sports_teams.team_name AND d2.round = 2
  LEFT JOIN draft_picks d3 ON d3.team_name = sports_teams.team_name AND d3.round = 3
  LEFT JOIN draft_picks d4 ON d4.team_name = sports_teams.team_name AND d4.round = 4

In some cases you may want the last iteration of the loop to produce a different result than other iterations. For example, if you’re creating a list of strings separated by commas, you might want a comma after every value except the last one. Liquid includes a forloop.last statement that makes this easy:

WHERE name IN (
  {% for name in list_of_names %}
    '{{name}}'
    {% unless forloop.last %}
      ,
    {% endunless %}
  {% endfor %}
)

For every iteration of the loop except the last one, forloop.last returns false. Therefore, the value in the unless statement—a comma—gets added to your query after every name except the last one.

This query contains two examples of a loop. This query, which uses the assign method below, shows one example.

Array Variables

Typically, for loops cycle through collections of values, such as iterable objects in Python or vectors in R. Liquid doesn’t allow you to create arrays of values the same way you would in most languages (e.g. list = ['candy','beans']). To create an array that you can iterate over in a for loop, you have to use the split filter on a delimited string and assign the result to a variable. For example:

{% assign food = 'candy,beans,pizza' | split: ","  %}

{% for item in food %}
  LEFT JOIN types_of_food {{ item }}
    ON {{ item }}.type = '{{ item }}'
{% endfor %}

The above code converts the comma delimited string ‘candy,beans,pizza’ to an array and assigns that array to the variable food. The for loop then iterates over each value in the array variable food.

Comments

Use {% comment %} and {% endcomment %} tags to instruct Mode to ignore whatever text or code is written between them.

Parameters

Parameters allow you to define forms that are configurable by viewers of your report and which return Liquid objects in your report’s code. Parameters are a great way to make reports more extensible, maintainable, and scalable.

Last updated May 17, 2018