Loops

​

Introduction

Loops allow you to add a SQL code for each item in a collection.

​

Syntax

The general syntax for defining a loop is as follows:

{#each collection as alias}
    Your SQL code template here
{/each}

Consider the scenario where you need to aggregate amounts by categories such as fashion, technology, and food. Instead of writing repetitive code by hand, you can use a loop to automate the process:

SELECT
    sum(case when category = 'fashion' then amount end) as fashion_amount,
    sum(case when category = 'technology' then amount end) as technology_amount,
    sum(case when category = 'food' then amount end) as food_amount
FROM table

To automate this, a loop can be constructed as shown below:

SELECT
    {#each ["fashion", "technology", "food"] as category}
        sum(case when category = '{category}' then amount end) as {category}_amount,
    {/each}
FROM table

The three key concepts are 1 Collection, 2 Alias and 3 SQL Code Template.

​

1 Collection

A collection refers to the list of items that the loop iterates over. There are two main types of collections that are supported:

​

runQuery

Executes a given query and uses the result set as the collection.

{#each runQuery('query_name') as alias}

To include values from another query, the one your are running, in the code is as follows. You can see SQL Code section to know more.

{#each runQuery('users_filtered') as users}
    name != users.first_name
{/each}

​

Manual Array

You can define a list of items as a collection. These items can be strings, dates, numbers, or variables.

SELECT 
    {#each ["fashion", "technology", "food"] as category}
        sum(case when category = '{category}' then amount end) as {category}_amount,
    {/each}
FROM table

Variables can be included within a manual list to introduce dynamic elements:

SELECT
    {#each [variable_manager, variable_ceo, variable_cto] as role_name}
        name != role_name
    {/each}
FROM table

​

2 Alias

The alias serves as a reference in the SQL code to each item within the collection. It is defined in the loop’s opening tag: {#each collection as alias}.

​

3 SQL Code

This is the template for the SQL code that will be executed for each item in the collection. It is enclosed between the {#each} and {/each} tags.

​

Loop Values

Values from each loop iteration can be included in the SQL code. For instance, to reference a column value from a query, use the syntax alias.column_name, e.g., users.first_name.

Refer to runQuery for examples.

​

Loop Conditions

Conditions can be applied to modify the SQL code based on the iteration count, such as omitting a comma on the last item to prevent SQL errors.

SELECT
    {#each collection as list}
        {list}
        {#if list < 4},{/if}  -- This avoids including a comma on the last item, preventing SQL errors.
    {/each}
FROM table

It is transformed into:

SELECT
    id,
    name,
    workspace_name -- It doesn't include a comma beacuse we added a condition to avoid it if item is < 4
FROM table

Conditional logic such as {:else} and {:if else} can also be utilized for more complex scenarios. For instace if the collection has no values we will have a fallback with {:else}

{#each members as user}
    WHERE user.signup > '20-08-2024'
{:else}
    WHERE user.signup < '20-08-2024'
{/each}