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


      
            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?
            table =
              ResultToMarkdown.convert(result[:pg_result], render_url_columns: opts[:render_url_columns])
          
            build_report_post(query, table, attach_csv: opts[:attach_csv], result:)
          end

github.com/discourse/discourse-data-explorer

lib/discourse_data_explorer/report_generator.rb

main


      
          def self.params_to_hash(query_params)
            params = JSON.parse(query_params)
            params_hash = {}
          
            if !params.blank?
              param_key, param_value = [], []
              params.flatten.each.with_index do |data, i|
                if i % 2 == 0
                  param_key << data
                else
                  param_value << data
                end
              end
          
              params_hash = Hash[param_key.zip(param_value)]
            end
          
            params_hash
          end

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:

github.com/discourse/discourse-data-explorer

FIX: Discourse automation reports with parameters

main ← fix/report-generator-parameters

opened 09:29AM - 13 Mar 25 UTC

Drenmi

+21 -17

### What is the problem? When setting up an automation to create a DM or a po…st with a report on a recurring basis, and using a Data Explorer query that has parameters, we encounter an error. ### Why is this happening? The `.params_to_hash` currently expects an array of arrays for the parameters, e.g.: ```ruby [["group_name", "admins"]] ``` but in reality they seem to be arrays of hashes: ```ruby [{ "key" => "group_name", "value" => "admins" }] ``` ### How come we only found out now? We do have tests for the report generator, and we do pass query parameters to it, but 1) we hard-code the parameters as an array of arrays and 2) the parameters aren't in the query used as a fixture. ### How does this fix it? This change makes `ReportGenerator.params_to_hash` work with arrays of hashes. It also preserves the ability to work with nested arrays in case this is used somewhere else.

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.

joffreyjaffeux · 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	9	1071	May 10, 2022
Param dropdown for group_id in data explorer query Feature data-explorer , completed	15	950	August 27, 2024
Utilizing Parameters in Data Explorer Queries Data & reporting data-explorer , sql-tutorial	4	1232	January 2, 2025
Automation script Schedule PM with Data Explorer results not sending PMs Bug automation	12	1093	July 6, 2023
Discourse Data Explorer Plugin official , data-explorer	18	76827	March 26, 2025

Syntax for Data Explorer params in Automation

Related topics