​
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.

Reference QueriesCast Methods