Utilizing Parameters in Data Explorer Queries

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. int is restricted to 32-bit numbers.
  • bigint: Similar to int, 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 to post_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 to time, but for dates.
  • datetime: Similar to time, 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 to user_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
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 =
-- 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

More Topics in this Series


These are great guides, thank you for posting them @SaraDev :slight_smile: :hugs:


can a field name in where clause be a parameter ? thanks
or the whole sql statement be a parameter to pass in from the rest endpoint /admin/plugin/explorer/queries/id/run

PSA: You can not use numbers in your parameter names, e.g. “foo123” will fail.

-- [params]
-- string       :foo123 = a

SELECT :foo123

results in

PG::SyntaxError: ERROR:  syntax error at or near ":"
LINE 10: SELECT :foo123