鼓励使用 'Notify Users' 发送私信

数据与报告
1

We want to encourage users to write more private messages about contentious or personal feedback on posts (and do it in a civil and friendly way…), rather than posting it public and possibly steering the conversation off-topic. Here’s the badge:

helpful-badge
helpful-badge847×245 14.2 KB

The query:

SELECT p.user_id, current_timestamp AS granted_at
FROM posts AS p
JOIN topics t on t.id = p.topic_id
WHERE t.archetype = 'private_message'
AND t.title LIKE 'Your post in "%"'
AND p.post_number = 1
AND p.like_count >= 1
AND (:backfill OR p.user_id IN (:user_ids))
GROUP BY p.user_id
HAVING count(*) >=1

I didn’t find a specific trigger for flag messages, so the query uses the default title “Your post in …”. I’d say the badge is easy to game or cheat in several ways, but it would still achieve it’s goal just by giving more visiblity to this feature and communicating that this is regarded positive action for the community.

3 个赞
2

这是否可以基于“通知用户”的 post_action？

类似这样：

SELECT pa.user_id, current_timestamp AS granted_at
FROM post_actions pa
  JOIN posts p ON p.id = pa.related_post_id
WHERE pa.post_action_type_id = 6
  AND p.like_count >= 1
  AND (:backfill OR p.user_id IN (:user_ids))
GROUP BY pa.user_id
HAVING COUNT(*) >= 1

实际上，我认为这指的是标记消息所依据的帖子的链接，而不是 PM。可能需要稍作调整。

将连接稍作更改，使 p.id = pa.related_post_id 即可，我认为。:+1:

我认为只有当您想授予多个（而不仅仅是一个）的银牌和金牌版本时，才需要 HAVING。

3 个赞