自动化脚本Schedule PM使用Data Explorer结果未发送PMs

Christian_Parker · 2023 年6 月 21 日 09:46

我们使用 Discourse 托管实例，并利用其提供的自动化插件及其脚本和触发器，但我们在接收来自“使用数据探索器结果安排 PM”脚本的 PM 时遇到问题。
看起来自动化触发器正在工作，并且数据探索器查询正在运行（此查询手动运行时可以正常工作并产生结果），但是我们没有收到任何 PM。我尝试将自己作为收件人，也尝试将“管理员”组作为收件人，但在两种情况下都没有收到 PM。
不确定我是否遗漏了什么明显的问题，但任何帮助都将不胜感激。

Canapin · 2023 年6 月 21 日 12:09

你好克里斯蒂安

你在 /logs 中看到任何相关内容了吗？

JammyDodger · 2023 年6 月 21 日 12:44

我也遇到了类似的情况：

数据浏览器查询似乎已运行：

但没有发送 PM。

我在 /logs 中看到了这个错误：

Message (5 copies reported)

Job exception: undefined method `to_sym' for nil:NilClass


Backtrace

/var/www/discourse/plugins/discourse-data-explorer/lib/result_to_markdown.rb:20:in `block (2 levels) in convert'
/var/www/discourse/plugins/discourse-data-explorer/lib/result_to_markdown.rb:18:in `each'
/var/www/discourse/plugins/discourse-data-explorer/lib/result_to_markdown.rb:18:in `each_with_index'
/var/www/discourse/plugins/discourse-data-explorer/lib/result_to_markdown.rb:18:in `block in convert'
/var/www/discourse/plugins/discourse-data-explorer/lib/result_to_markdown.rb:15:in `each'
/var/www/discourse/plugins/discourse-data-explorer/lib/result_to_markdown.rb:15:in `convert'
/var/www/discourse/plugins/discourse-data-explorer/lib/report_generator.rb:20:in `generate'
/var/www/discourse/plugins/discourse-data-explorer/plugin.rb:117:in `block (4 levels) in activate!'
/var/www/discourse/plugins/discourse-automation/app/models/discourse_automation/automation.rb:83:in `trigger!'
/var/www/discourse/plugins/discourse-automation/app/jobs/scheduled/discourse_automation_tracker.rb:36:in `run_pending_automation'

如果我尝试手动触发自动化，我也会收到一个“500”错误，并在日志中看到这个（略有不同的）错误：

Message (4 copies reported)

NoMethodError (undefined method `to_sym' for nil:NilClass)
app/controllers/application_controller.rb:418:in `block in with_resolved_locale'
app/controllers/application_controller.rb:418:in `with_resolved_locale'
lib/middleware/omniauth_bypass_middleware.rb:74:in `call'
lib/content_security_policy/middleware.rb:12:in `call'
lib/middleware/anonymous_cache.rb:369:in `call'
config/initializers/100-quiet_logger.rb:20:in `call'
config/initializers/100-silence_logger.rb:29:in `call'
lib/middleware/enforce_hostname.rb:24:in `call'
lib/middleware/request_tracker.rb:228:in `call'

Backtrace

plugins/discourse-data-explorer/lib/result_to_markdown.rb:20:in `block (2 levels) in convert'
plugins/discourse-data-explorer/lib/result_to_markdown.rb:18:in `each'
plugins/discourse-data-explorer/lib/result_to_markdown.rb:18:in `each_with_index'
plugins/discourse-data-explorer/lib/result_to_markdown.rb:18:in `block in convert'
plugins/discourse-data-explorer/lib/result_to_markdown.rb:15:in `each'
plugins/discourse-data-explorer/lib/result_to_markdown.rb:15:in `convert'
plugins/discourse-data-explorer/lib/report_generator.rb:20:in `generate'
plugins/discourse-data-explorer/plugin.rb:117:in `block (4 levels) in activate!'
plugins/discourse-automation/app/models/discourse_automation/automation.rb:83:in `trigger!'
plugins/discourse-automation/app/controllers/discourse_automation/automations_controller.rb:10:in `trigger'

我们把它移到 Bug 频道，看看是否有人能解决。

JammyDodger · 2023 年6 月 21 日 13:00

经过进一步调查，我认为这可能与您尝试运行的查询类型有关。我刚刚尝试了一个非常简单的查询，并且成功运行并发送了一条私人消息。您能否分享一下您的查询是什么？

Christian_Parker · 2023 年6 月 21 日 13:38

感谢您对此事的关注。
查询并非完全简单，看起来像这样：

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

正如我所说，它手动运行并产生了结果。

Canapin · 2023 年6 月 21 日 13:40

您说的手动运行是指从数据浏览器触发查询，还是从自动化插件触发脚本？

Christian_Parker · 2023 年6 月 21 日 13:40

从数据浏览器。

JammyDodger · 2023 年6 月 21 日 14:56

我认为这与数据浏览器在将裸 ID 转换为可用链接时所做的 user_id 样式魔术有关。如果我按原样运行您的报告，它会像您看到的那样出错，但如果我从 SELECT 中删除 t.user_id 和 t.last_post_user_id，它确实有效。

如果我将它们转换为纯用户名，通过自动化似乎也能正常工作：


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

不过，我们也已联系更专业的人员进行更详细的查看。

不过另外，我不确定您的查询是否能达到您想要的效果。这与解决方案有关吗？因为我在结果中看到了很多 PM。

Christian_Parker · 2023 年6 月 21 日 15:44

好的，我将尝试更改用户 ID 来进行测试。

我最初没有编写此查询，因此我还不确定它是否能提供我们想要的结果，所以我可能会重写它。

riking · 2023 年6 月 22 日 01:50

问题出在这行代码：https://github.com/discourse/discourse-data-explorer/blob/705753216cc632b4f6505d2000926ab3b73d8628/lib/result_to_markdown.rb#L20

related = relations.dig(colrender[col_index].to_sym) if col_index < colrender.size

末尾的if条件不正确：colrender应该是稀疏的，而不是紧凑的。如果某些列（但不是全部）提供额外的渲染数据，它将包含null。

正确的检查可能是unless colrender[col_index].nil?

此外，此代码似乎忽略了url、reltime和html渲染类型，因为它们不是ActiveRecord类。

Canapin · 2023 年6 月 23 日 13:04

对我来说，似乎无论数据浏览器查询是什么，我都无法成功触发此自动化脚本。例如：

SELECT username from users
LIMIT 10

从自动化手动触发时出现 500 错误：

消息（报告了 3 份副本）

TypeError (Nil is not a valid JSON source.)
app/controllers/application_controller.rb:418:in `block in with_resolved_locale'
app/controllers/application_controller.rb:418:in `with_resolved_locale'
lib/middleware/omniauth_bypass_middleware.rb:74:in `call'
lib/content_security_policy/middleware.rb:12:in `call'
lib/middleware/anonymous_cache.rb:369:in `call'
config/initializers/100-quiet_logger.rb:20:in `call'
config/initializers/100-silence_logger.rb:29:in `call'
lib/middleware/enforce_hostname.rb:24:in `call'
lib/middleware/request_tracker.rb:228:in `call'

回溯

plugins/discourse-data-explorer/lib/report_generator.rb:42:in `parse'
plugins/discourse-data-explorer/lib/report_generator.rb:42:in `params_to_hash'
plugins/discourse-data-explorer/lib/report_generator.rb:15:in `generate'
plugins/discourse-data-explorer/plugin.rb:117:in `block (4 levels) in activate!'
plugins/discourse-automation/app/models/discourse_automation/automation.rb:83:in `trigger!'
plugins/discourse-automation/app/controllers/discourse_automation/automations_controller.rb:10:in `trigger'
actionpack (7.0.4.3) lib/action_controller/metal/basic_implicit_render.rb:6:in `send_action'
actionpack (7.0.4.3) lib/abstract_controller/base.rb:215:in `process_action'
actionpack (7.0.4.3) lib/action_controller/metal/rendering.rb:53:in `process_action'
actionpack (7.0.4.3) lib/abstract_controller/callbacks.rb:234:in `block in process_action'

环境

HTTP HOSTS: discourse.canapin.dev

davidb · 2023 年7 月 4 日 09:15

此 PR 应能修复这些问题：

github.com/discourse/discourse-data-explorer

FIX: Scheduled PM with Data Explorer Results not sending PM (#250)

committed 06:18AM - 04 Jul 23 UTC

dbattersby

+38 -4

This PR fixes 2 issues that were picked up by users for the Scheduled Data Explo…rer Report automation script and a couple of small improvements to better match the format of manual data explorer query results. The first issue is that within result_to_markdown the colrender contains null values, and we are currently checking length (previously treated as a packed array but it is actually a sparse array). Therefore we can check if the current index of the array is null rather than checking the size of the array. The second issue addresses the blank query_params field. When the data explorer script does not require any params to be passed in via the automation script then it will have a nil value, however it should be defaulted to {} within the plugin. To improve formatting the markdown table for PMs is now aligned to left and where values are substituted (for example user_id becomes username) we then include the id within brackets, for example: user_id becomes username (user_id)

davidb · 2023 年7 月 6 日 09:26

此主题在 2 天后自动关闭。不再允许回复。

话题		回复	浏览量
Can't run query in data explorer plugin Support data-explorer , dev-install	3	780	2018 年7 月 23 日
Automation doesn't send to group Bug automation	18	236	2025 年5 月 9 日
Data Explorer NoMethodError (undefined method `username' for nil:NilClass) Bug	8	1558	2017 年1 月 9 日
Turn Data Explorer query results into CSV to attach to Discourse automated emails Feature email , data-explorer , completed	7	767	2024 年8 月 30 日
Discourse Data Explorer Plugin official , data-explorer , included-in-core	31	80385	2025 年10 月 22 日

自动化脚本Schedule PM使用Data Explorer结果未发送PMs

相关话题