Syntax for Data Explorer params in Automation

ganncamp · March 5, 2025, 7:08pm

I have a Data Explorer query that accepts a ‘group_name’ parameter

I would like to automate running it for each relevant group and sending the group the results once a week.

But for the life of me, I cannot figure out the syntax to use for the parameter. Here’s what I’m presented for that in a brand new automation:

I find this… unhelpful.

I’ve tried several variations here. When I don’t get an error from my attempts (found in the error logs), I seem to get nothing at all.

If I use a query with no parameters, it runs like greased lightning. How do I make it work for queries that do need parameters?

Arkshine · March 5, 2025, 10:46pm

I can reproduce the issue as well.

In your example, let’s say you want to check the moderators and admins groups, you are supposed to have:

key: group_name
value: moderators,admins

Technical stuff to help, don’t mind me.
I tried to understand looking at the code, and I think there is an issue here:

github.com/discourse/discourse-data-explorer

lib/discourse_data_explorer/report_generator.rb

main


      
          
            result = DataExplorer.run_query(query, params)
            query.update!(last_run_at: Time.now)
          
            return [] if opts[:skip_empty] && result[:pg_result].values.empty?
            table =
              ResultToMarkdown.convert(result[:pg_result], render_url_columns: opts[:render_url_columns])
          
            build_report_pms(query, table, recipients, attach_csv: opts[:attach_csv], result:)
          end
          
          def self.generate_post(query_id, query_params, opts = {})
            query = DiscourseDataExplorer::Query.find(query_id)
            return {} if !query
          
            params = params_to_hash(query_params)
          
            result = DataExplorer.run_query(query, params)
            query.update!(last_run_at: Time.now)
          
            return {} if opts[:skip_empty] && result[:pg_result].values.empty?

github.com/discourse/discourse-data-explorer

lib/discourse_data_explorer/report_generator.rb

main


      
            build_report_post(query, table, attach_csv: opts[:attach_csv], result:)
          end
          
          def self.params_to_hash(query_params)
            params = JSON.parse(query_params)
          
            params.map { |p| p.is_a?(Hash) ? [p["key"], p["value"]] : p }.to_h
          end
          
          def self.build_report_pms(query, table = "", targets = [], attach_csv: false, result: nil)
            pms = []
            upload = create_csv_upload(query, result) if attach_csv
          
            targets.each do |target|
              name = target[0]
              pm_type = "target_#{target[1]}s"
          
              pm = {}
              pm["title"] = I18n.t(

The params are transformed before run_query is called.
Let’s say you have this original value:
[{"key":"group_names","value":"admins,moderators"}]
The transformed value will be:
{{"key"=>"group_names", "value"=>"admins,moderators"}=>nil}

github.com/discourse/discourse-data-explorer

lib/discourse_data_explorer/data_explorer.rb

main


      
          def self.run_query(query, req_params = {}, opts = {})
            # Safety checks
            # see test 'doesn't allow you to modify the database #2'
            if query.sql =~ /;/
              err = ValidationError.new(I18n.t("js.errors.explorer.no_semicolons"))
              return { error: err, duration_nanos: 0 }
            end
          
            query_args = {}
            begin
              query_args = query.cast_params req_params
            rescue ValidationError => e
              return { error: e, duration_nanos: 0 }
            end
          
            time_start, time_end, explain, err, result = nil
            begin
              ActiveRecord::Base.connection.transaction do
                # Setting transaction to read only prevents shoot-in-foot actions like SELECT FOR UPDATE
                # see test 'doesn't allow you to modify the database #1'
                DB.exec "SET TRANSACTION READ ONLY"

github.com/discourse/discourse-data-explorer

app/models/discourse_data_explorer/query.rb

main


      
          def cast_params(input_params)
            result = {}.with_indifferent_access
            self.params.each do |pobj|
              result[pobj.identifier] = pobj.cast_to_ruby input_params[pobj.identifier]
            end
            result
          end

However, cast_params seems to expect {"group_names"=>"admins,moderators"}

I tried to test this naive change, and the params worked.

def self.params_to_hash(query_params)
  params = JSON.parse(query_params)
  params_hash = {}

  params.each do |param|
    key = param["key"]
    value = param["value"]
    params_hash[key] = value
  end

  params_hash
end

sam · March 6, 2025, 10:03pm

wow thanks for the debugging here we will have a look at this in the upcoming week.

ted · March 13, 2025, 7:47am

That’s some fantastic spelunking, @Arkshine!

I’m looking into this now. When I read the original code I get the impression that it expects the parameters to be an array of arrays, so I’m a bit weary of just throwing the existing stuff out. Will try to figure out when this might happen.

Will post here when I find something.

ted · March 13, 2025, 10:01am

There was indeed a bug here, and partly thanks to the investigation of @Arkshine, it was relatively easy to fix:

https://github.com/discourse/discourse-data-explorer/pull/363

This has been merged already, so once your site has been deployed you should be unblocked @ganncamp.

If I understand correctly, you want an automation that is a one-to-one mapping between a group and its report? (I.e. group_a receives a report with group_a data, etc.)

There is (currently) no connection between the recipient list and the report parameters, so to accomplish that you’d need to set up one automation per group. In this case you might want to change the parameter to group_id.

ganncamp · March 13, 2025, 11:51am

Great news @ted!

Yes, being able to foreach a set of groups was going to be my next request

ted · March 14, 2025, 2:19am

This does indeed seem like a convenient thing to have, especially for sites with lots and lots of groups. I’ll note down the request, but I can’t say when we’ll get to it.

j.jaffeux · March 17, 2025, 10:09am

This topic was automatically closed 3 days after the last reply. New replies are no longer allowed.

Topic		Replies	Views
`DataExplorer::ValidationError: Missing parameter` when running Data Explorer queries with [params] via API Data & reporting rest-api , sql-query	8	1341	April 10, 2022
How to add query parameters to Data Explorer? Development	7	1190	November 25, 2020
Populate Data Explorer params with URL params Feature data-explorer , completed	12	1779	September 3, 2025
Param dropdown for group_id in data explorer query Feature data-explorer , completed	17	1432	June 5, 2025
Using Parameters in Data Explorer Queries Data & reporting data-explorer , sql-tutorial	6	2000	February 10, 2026

Syntax for Data Explorer params in Automation

AI-generated summary

Related topics