Parameters with optional values

In Flowtrail AI, parameters can be made optional, allowing for more flexible queries. When a parameter is not required, you can use conditional logic in your SQL code to include or exclude certain conditions based on whether a value has been selected by the user. If the user selects a value, the query will filter results based on that value; otherwise, the query will run without the optional condition.

Example Query with Optional Parameter

Here’s an example to illustrate how you can conditionally include a parameter in your SQL query:

example.sql

SELECT f.title, f.description, c.name AS category_name
FROM film f
JOIN film_category fc ON f.film_id = fc.film_id
JOIN category c ON fc.category_id = c.category_id
{% if category %}
  WHERE c.name = '{{category}}'
{% endif %}
LIMIT 10;

Explanation

• Base Query: The query selects film titles and descriptions along with their category names from a set of joined tables
• Conditional Logic: The {% if category %} block checks if the category parameter has been provided. If it has, the WHERE clause filters films based on the selected category. If the category parameter is not provided, the query runs without the WHERE clause, returning results for all categories.

Steps to Implement Optional Parameters

Implementing optional parameters in Flowtrail AI involves defining the parameters, incorporating conditional logic in your SQL query, and executing the query dynamically based on user inputs. Follow these steps to enhance the flexibility and adaptability of your dataset queries.

Define Parameters

Create your parameters in the parameter list as usual. Ensure that optional parameters are not marked as required. In this example, we use the select parameter with predefined category values

Write Conditional SQL with Conditional Parameter

Use conditional logic in your SQL query to check if an optional parameter is provided. Structure the query to include or exclude conditions based on the presence of the parameter.

Execute Query

When the query is executed, Flowtrail AI will dynamically adjust the SQL based on whether the optional parameter values are provided.

We are not selected the parameter value, so the query will run without the WHERE clause, returning results for all categories. The executed script can see with the result.

Here we have selected the category value as ‘Childres’, so the query will run with the WHERE clause, filtering results based on the selected category.