Automation script Schedule PM with Data Explorer results not sending PMs

bug
16

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. :+1: :slight_smile:

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?

3 Likes