Run Query
Dynamically executes SQL queries, ideal for generating SQL code with runtime-known values.
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:
id | payment_method |
---|---|
1 | cash |
2 | credit_card |
3 | transfer |
4 | promotion |
You can run a query from another one. For instace in purchases_validation
we are going to run the payment_methods
one.
{results = runQuery('payment_methods')}
Then you can access a specific row with:
SELECT *
FROM table
WHERE payment_method IS NOT null
{#each row in results}
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:
- Identify Different Payment Methods:
-- payment_methods.sql
SELECT DISTINCT payment_method FROM payments
ORDER BY 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.
{ }
-- my_model.sql
{results = runQuery('payment_methods')} -- Here we put in a variable the results from the payment_methods query
SELECT
order_id,
{#each row in results} -- 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.