​
Introduction

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

​
Syntax

The syntax for a loop is defined by three main components: 
{#each collection as item, index}
  /* code */
{/each}
  • Collection: An iterable object that contains the items to be looped over. This can be a manual array, an expression or an existing variable.
  • Item: The current element in the collection for this iteration. This will define a new variable only available within the loop.
  • Index (optional): The current index of the item in the collection, starting by 0. As with the item, this will define a new variable only available within the loop.
Let’s see an example of how it works: 
SELECT
{#each ["a", "b", "c", "d", "e"] as column_name, i}
  {i} AS {column_name},
{/each}
You can also add an {:else} block after the loop, which would be executed if the collection is empty: 
{#each collection as item, index}
  /* code for each item */
{:else}
  /* code if there are no items */
{/each}

​
Use cases

​
Avoiding repetitive code

Now, consider the scenario where you need to aggregate amounts by categories such as fashion, technology, food and sport. The SQL code would look like this: 
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
    sum(case when category = 'sport' then amount end) as sport_amount
FROM table
Instead of writing repetitive code, you can use a loop to iterate over the categories and generate the SQL code dynamically: 
{categories = ["fashion", "technology", "food", "sport"]}

SELECT
    {#each categories as category_name, i}
        sum(case when category = {category_name} then amount end) as {category_name}_amount

        /* Add a comma at the end of each item except the last one */
        {#if i + 1 < categories.length} , {/if}
    {/each}
FROM table

​
Iterating over other query results

You can also use loops to iterate over the results of another query. For example, if you have a list of users and you want to get the total amount spent by each user, you can use a loop to iterate over the user IDs: 
{results = runQuery('users')}

SELECT *
FROM table
WHERE user_id IN (
    {#each results as row, i}
        {#if i > 0} , {/if} /* Add a comma before each item except the first one */
        {row.id}
    {/each}
)

​
Iterating params for an IN clause

Imagine you have a multi select in your application that allows selecting multiple values. You can pass the selected values as an array to the query param and iterate over it to filter the results. In this case we pass hiring_period that can be some of these ['Mid', 'New', 'Old'] 
SELECT * FROM employees
{hiringPeriod = param('hiring_period', [])}

{#if hiringPeriod.length > 0}
  WHERE hiring_period IN (
    {#each hiringPeriod as period, index}
      {#if index > 0},{/if}
      {period}
    {/each}
  );
{/if}