Users Christian_Parker and JammyDodger report that the ‘Schedule PM with Data Explorer results’ automation script fails to send private messages despite running successfully in Data Explorer. Errors in /logs indicate a NoMethodError: undefined method 'to_sym' for nil:NilClass originating from result_to_markdown.rb, triggered when the query includes user_id columns. JammyDodger finds that replacing user_id with username resolves the issue, suggesting Data Explorer’s automatic user ID-to-link conversion is incompatible with automation. riking identifies the root cause: a flawed if condition in the code that assumes colrender is packed rather than sparse, and recommends checking unless colrender[col_index].nil?. Separately, Canapin reports a TypeError: Nil is not a valid JSON source even with simple queries, indicating broader automation compatibility issues. davidb confirms these bugs are addressed in a recent PR to the Data Explorer plugin. The issue appears tied to how Data Explorer handles column rendering types and JSON parsing during automation-triggered report generation.
We use a Discourse hosted instance and make use of the provided Automation plugin with its range of scripts and triggers, however we are having issues receiving PM’s from the ‘Schedule PM with data explorer results’ script.
It looks like the Automation trigger is working and the Data explorer query is being run (this query does work and produces results when run manually), however we are not receiving any PM’s following this. I have tried with myself as the recipient and also the ‘Admin’ group but in both cases no PM’s are received.
Not sure if I’m missing something obvious here but any help would be appreciated.
After a bit of further investigation, I think it may be connected to the type of query you’re trying to run. I’ve just tried with a very simple one and I have managed to get it to run and send a PM. Could you share what your query is?
Thanks for looking into this.
The query is not totally simple and looks like this:
WITH
ua AS (
SELECT target_topic_id, COUNT(id) FROM user_actions
WHERE action_type = 15
GROUP BY target_topic_id
)
SELECT
t.id,
t.title,
t.created_at,
t.last_posted_at,
t.views,
t.posts_count,
t.user_id,
t.last_post_user_id
FROM topics t
INNER JOIN users us ON us.id = t.user_id
LEFT JOIN ua ON ua.target_topic_id = t.id
WHERE t.deleted_at IS NULL
AND t.closed = false
AND t.archived = false
AND t.visible = true
AND ua.target_topic_id IS NULL
AND us.username_lower != 'system'
AND t.created_at > now() - INTERVAL '7' DAY
ORDER BY created_at DESC
As I say, it does run manually and produce results.
I think it’s something to do with the user_id style magic the data explorer does when it converts them from bare ids to useable links. If I run your report as is it errors out just like you’re seeing, but if I remove both t.user_id and t.last_post_user_id from the SELECT it does work.
If I convert those into plain usernames it also seems to work properly through the automation:
WITH
ua AS (
SELECT target_topic_id, COUNT(id) FROM user_actions
WHERE action_type = 15
GROUP BY target_topic_id
)
SELECT
t.id,
t.title,
t.created_at,
t.last_posted_at,
t.views,
t.posts_count,
us.username,
u2.username
FROM topics t
INNER JOIN users us ON us.id = t.user_id
LEFT JOIN ua ON ua.target_topic_id = t.id
JOIN users u2 ON u2.id = t.last_post_user_id
WHERE t.deleted_at IS NULL
AND t.closed = false
AND t.archived = false
AND t.visible = true
AND ua.target_topic_id IS NULL
AND us.username_lower != 'system'
AND t.created_at > now() - INTERVAL '7' DAY
ORDER BY created_at DESC
Though we’ve also pinged someone more knowledgeable to have a look in more detail.
Though separately, I’m not sure your query does what you want it to. Is it somehow Solution related, as I’m seeing a lot of PMs in my results?
related = relations.dig(colrender[col_index].to_sym) if col_index < colrender.size
The if condition on the end is incorrect: colrender is supposed to be sparse, not packed. It will contain nulls if some but not all columns provide extra rendering data.
The correct check would probably be unless colrender[col_index].nil?
Also, this code appears to neglect the url, reltime, and html rendering types because they’re not ActiveRecord classes.
