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.
List Parameters
int_list: Enter comma-separated integers, becomes comma-separated integers in the query.
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.category_id: Ensures the specified category exists.group_id: Ensures the specified group exists.badge_id: Ensures the specified badge exists.
Boolean Parameters
boolean: Shows a checkbox.null boolean: Shows a dropdown, allowing for an empty input.
String Parameters
string: Freeform textbox, becomes a text value.string_list: Comma-separated freeform text.
Time Parameters
time: Textbox that is string-casted to the appropriate type in the query. No client-side validation.date: Similar totime, but for dates.datetime: Similar totime, but includes both date and time.
User 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.
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.
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
