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.
