Badge for posts with Likes from a specific group

Community Building Data & reporting
25

在删除了一些导致测试出错的点赞后，我认为这是 OP 中符合您要求的修订版本：:slight_smile:

SELECT p.user_id, MAX(p.created_at) granted_at
FROM badge_posts p
WHERE (SELECT COUNT(*)
        FROM post_actions pa
        WHERE pa.post_id = p.id
         AND post_action_type_id = 2
         AND deleted_at IS NULL
         AND pa.user_id IN (SELECT gu.user_id FROM group_users gu WHERE gu.group_id = 3)
       ) >= 5
    AND p.user_id >= 0
GROUP BY p.user_id
HAVING COUNT(*) >= 10

staff love
staff love1251×405 16.4 KB

它基于 badge_posts 视图，因此只计算来自公共类别的帖子，您可能需要根据您的论坛/类别设置来考虑这一点。另外，使用 CURRENT_TIMESTAMP 作为 granted_at 是另一个选项，但这可能取决于个人喜好。