​
Introduction

The runQuery function allows you to dynamically execute SQL queries based on the results of a previous query. This functionality is particularly useful when you need to dynamically generate column names or perform operations based on values that are not known until runtime.

​
Syntax

The syntax is runQuery + ('query_file_name_to_run'). This would return the whole query.

The payment_methods.sql results are:

idpayment_method
1cash
2credit_card
3transfer
4promotion

You can run a query from another one. For instace in purchases_validation we are going to run the payment_methods one.

purchases_validation
{results = runQuery('payment_methods')}

Then you can access a specific row with:

payment_validation
SELECT *
FROM table
WHERE payment_method IS NOT null
{#each results as row}
  OR payment_method = {payment_type = row['payment_method']}
{/each}

It is transformed into:

SELECT *
FROM table
WHERE payment_method IS NOT null
  OR payment_method = cash
  OR payment_method = credit_card
  OR payment_method = transfer
  OR payment_method = promotion

​
Example

Consider a scenario where you have a payments table and you want to generate a table that shows the total amount for each payment method, along with a column for the total amount. The steps would be as follows:

  1. Identify Different Payment Methods:
-- payment_methods.sql
SELECT DISTINCT payment_method FROM payments
ORDER BY 1
  1. Generate Report Based on Distinct Payment Methods:

Generate a report based on distinct payment methods: Using the results of the first query, runQuery is called to dynamically generate a second SQL query. This second query aggregates the payment amounts by each different payment method obtained from the first query.

Remember to enclose any latitude logic in curly braces { }.

-- my_model.sql
{results = runQuery('payment_methods')}   -- Here we put in a variable the results from the payment_methods query
SELECT
  order_id,
  {#each results as row}    -- We create a loop to add the specfied SQL code for each row after run payment_methods
    {payment_type = row['payment_method']}    -- We create a variable that contain the value from payment_method column
    SUM(
        CASE WHEN payment_method = {payment_type}    -- We compare the current value of the loop with the
        THEN amount END
    ) AS {payment_type}_amount,
  {/each}
  SUM(amount) AS total_amount
FROM payments
GROUP BY 1

This approach is essential for creating dynamic SQL queries that adjust based on actual database contents, which is not directly possible with standard SQL due to its static nature.