ganncamp
March 5, 2025, 7:08pm
1
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?
1 Like
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_namemoderators,admins
Technical stuff to help, don’t mind me.
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
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.[{"key":"group_names","value":"admins,moderators"}]{{"key"=>"group_names", "value"=>"admins,moderators"}=>nil}
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"
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
6 Likes
sam
March 6, 2025, 10:03pm
3
wow thanks for the debugging here
4 Likes
we will have a look at this in the upcoming week.