Parameters are a powerful tool that can be used in Data Explorer queries on Discourse. Parameters allow for more dynamic and customizable queries, and instead of hard-coding values in your queries, you can declare variables that will prompt for input when the query is run.
Declaring a Parameter
To declare a parameter, you can use the following syntax:
-- [params]
-- int :parameter_name = 10
The parameter’s section of the query will always start with -- [params], followed by each type of parameter on a new line, where parameter_name would be replaced by the name for your parameter.
This will create a field where you can input different values each time you run the query.
Types of Parameters
When declaring parameters in your Data Explorer queries, you can specify different types of inputs. Here are the available parameter types and their descriptions:
Numeric Parameters
int: Shows a number input, becomes a numeric value.intis restricted to 32-bit numbers.bigint: Similar toint, but can be larger.double: Allows decimal values.
The correctness of the numeric parameters will be verified on the front end.
String Parameters
string: Freeform textbox, becomes a text value.
List Parameters
int_list: Enter comma-separated integers, becomes comma-separated integers in the query.string_list: Similar toint_list, but for strings.
Specific ID Parameters
post_id: Numeric input; ensures the specified post exists on the forum before running the query.topic_id: Similar topost_id, but for topics.badge_id: Ensures the specified badge exists.
Boolean Parameters
boolean: Shows a checkbox.null boolean: Shows a dropdown, allowing for an empty input.
Time Parameters
time: Displays a time picker input.date: Displays a date picker input.datetime: Displays a input box includes both date and time.
Selector Parameters
user_id: Shows the Discourse user selector box, and becomes the numeric user ID.user_list: Similar touser_id, but allows for multiple users, becoming a comma-separated list of the numeric user IDs.group_id: Similar touser_id, but for groups.group_list: Similar touser_list, but for groups.category_id: Similar touser_id, but for categories.
Using List Parameters
When using list parameters (int_list, string_list, user_list), special care must be taken to avoid syntax errors. Here is an example of correctly using a list parameter:
-- [params]
-- user_list :the_user_ids
SELECT SUM(length(bio_raw))
FROM user_profiles
WHERE user_id IN (:the_user_ids)
Null Parameters
You can also allow for an empty input by prefixing the parameter type with null. This means that it’s not necessary to provide a value for that parameter when running the query.
Here’s some examples of how you would declare such parameters:
-- [params]
-- null int :null_int
-- null boolean :null_boolean
-- null string :null_string
In the above SQL, null_int, null_boolean, and null_string are parameters that can be left empty when running the query.
Let’s see how these types of parameters can be used in a query:
-- [params]
-- null int :post_id
-- null string :username
SELECT *
FROM users
WHERE (id = :post_id OR :post_id IS NULL)
AND (username = :username OR :username IS NULL)
In this query, if post_id or username is not provided (i.e., left as null), the query will ignore that part of the WHERE clause. This allows for more flexible queries where some conditions are optional.
Front-end Validation
Most types of parameters will be validated on the front end. These validations include required but unfilled inputs, invalid numeric input, non-existent categories or groups, malformed times, etc. For invalid input, the reason for the error will be displayed in the form, and the run query operation will be rejected.
Additional Examples
Here are some additional examples of declaring different types of parameters:
-- [params]
-- int :int = 3
-- bigint :bigint = 12345678912345
-- boolean :boolean
-- null boolean :boolean_three = #null
-- string :string = little bunny foo foo
-- date :date = 14 jul 2015
-- time :time = 5:02 pm
-- datetime :datetime = 14 jul 2015 5:02 pm
-- double :double = 3.1415
-- string :inet = 127.0.0.1/8
-- user_id :user_id = system
-- post_id :post_id = http://localhost:3000/t/adsfdsfajadsdafdsds-sf-awerjkldfdwe/21/1?u=system
-- topic_id :topic_id = /t/-/21
-- int_list :int_list = 1,2,3
-- string_list :string_list = a,b,c
-- category_id :category_id = meta
-- group_id :group_id = admins
-- user_list :mul_users = system,discobot
