Query Variables
Make your queries dynamic and link together objects.
Query variables allow values to be attached to variables so that a single value can easily be expressed multiple times within a query, or quickly changes to affect the results of a query. Query variables are created by wrapping the desired variable name in double curly braces anywhere inside a query. An input box in the right sidebar will appear for each query variable created. As an example, this query will return a list of all countries where
{{language}} is an officially recognized language:select
country.Name,
countrylanguage.Language,
countrylanguage.Percentage
from
countrylanguage
join country on country.Code = countrylanguage.CountryCode
where
countrylanguage.Language = {{language}}
and countrylanguage.IsOfficial = 1
order by countrylanguage.Percentage desc
Once a query variable is added to a query, it will appear in the right sidebar as an empty variable. Enter a value into the variable input to attach a value to it. For this example, we’ll use ‘English’:
Insert a query variable by typing {{variable}}
Query variables support String, Number, Date, and Boolean data types. For example, the above query can be modified to return a list of all countries where
{{language}} is officially recognized and the population is greater than {{min_population}}:select
country.Name,
countrylanguage.Language,
countrylanguage.Percentage
from
countrylanguage
join country on country.Code = countrylanguage.CountryCode
where
countrylanguage.Language = {{language}}
and countrylanguage.IsOfficial = 1
and country.Population > {{min_population}}
order by countrylanguage.Percentage des
For this example, we'll look for Spanish-speaking countries with populations of more than five million people:
Query variables with different data types
Note: No further configuration is required, no matter which type of variable is used. Arctype will automatically detect the type of variable inputted.
You may want to use a query variable inside a string in Arctype. For example take this querySELECT * FROM imp_cities
SELECT * FROM imp_cities
WHERE name LIKE '%city_name%'
It would be great to use a variable instead of
city_name. This can be achieved using CONCATSELECT * FROM imp_cities
WHERE name LIKE CONCAT('%', {{var}}, '%')
Here
{{var}} is our query variable and this query works using a string comparison.You can add queries that include query variables to dashboards to create dynamic dashboards with charts and tables that change based on a text or date input.
First create a query that uses a query variable as above. Add this to a Dashboard by clicking on the 3 dots in the top right of the Results Pane and choosing
Add to Dashboard.Second, once the query is added to a dashboard as a chart or table, right click on its title and choose
Configure Chart.
Configure a chart or table to link it to a Dashboard input using a query variable.
Third, in the right hand context menu, you will see inputs to the query that are titled after the query variables you used. In the image above,
{{end_date}} and {{start_date}} are both query variables that are shown in this menu. Next to these inputs, click the gray link button. If an input such as a text input or a date picker has already been linked to this query variable, the link will be pink instead of gray.
Click the link button in a chart or table that uses a query that contains query variables. Then, select which input in the dashboard you want to link the query to.
Finally, you will see a menu to choose which dashboard input, such as a text input or date picker. In the example above we have a date picker input named
endDate that has already been added to the dashboard. We select its value property to link the user selected date to the end_date query variable. Select your input and the value you would like to use from it.This feature also applies for tables and rows which means you can build charts which change based on clicking a row in a table. For more examples of the surprisingly complex charts that can be built, see this blog post on visualizing sales data in a dashboard.
Last modified 1yr ago