Badge for posts with Likes from a specific group

[fefrei (Felix Freiberger)]

As already mentioned here:

Grant a badge to everyone having posted at least <POST_COUNT> posts in the category <CATEGORY_NAME> that have received at least <LIKE_COUNT> likes by users in the group <TEAM_NAME>. Similar to the bug reporter badge here, but can require more than one like.

SELECT p.user_id, min(p.created_at) granted_at, MIN(p.id) post_id
FROM badge_posts p
JOIN topics t ON t.id = p.topic_id
WHERE category_id = (
        SELECT id FROM categories WHERE name ilike '<CATEGORY_NAME>'
    ) AND (
        SELECT count(*)
        FROM post_actions pa
        WHERE pa.post_id = p.id
            AND post_action_type_id = (
                SELECT id FROM post_action_types WHERE name_key = 'like'
            ) AND pa.user_id IN (
                SELECT gu.user_id
                FROM group_users gu
                WHERE gu.group_id = ( SELECT id FROM groups WHERE name ilike '<TEAM_NAME>' ) 
            )
    ) >= <LIKE_COUNT>
    AND p.post_number = 1
    AND p.user_id >= 0
GROUP BY p.user_id
HAVING count(*) >= <POST_COUNT>

[Firepup650 (Firepup Sixfifty)]

我一直在摆弄这个，几乎成功了，但我无法按照我想要的方式将其应用于所有类别。有没有简单的方法可以做到这一点？

[Lilly (Lillian Louis)]

你好 @Firepup650 也许可以试试这个，它在我的实例上运行成功了。

<CATEGORY NAME> = 区分大小写的类别名称（不是 slug）
<GROUP> = 用户组名称（例如：Staff、Trust_level_0）
<MINIMUM LIKE COUNT> = 你想设置的最小点赞数
<POST COUNT THRESHOLD> = 最小帖子数

SELECT p.user_id, min(p.created_at) granted_at, MIN(p.id) post_id
FROM badge_posts p
JOIN topics t ON t.id = p.topic_id
WHERE t.category_id = (
        SELECT id FROM categories WHERE name ilike '<CATEGORY NAME>'
    ) AND (
        SELECT count(*)
        FROM post_actions pa
        WHERE pa.post_id = p.id
            AND post_action_type_id = (
                SELECT id FROM post_action_types WHERE name_key = 'like'
            ) AND pa.user_id IN (
                SELECT gu.user_id
                FROM group_users gu
                WHERE gu.group_id = ( SELECT id FROM groups WHERE name ilike '<GROUP NAME>' )
            )
    ) >= <MINIMUM LIKE COUNT>
    AND p.user_id >= 0
GROUP BY p.user_id
HAVING count(*) >= <POST COUNT THRESHOLD>

对于多个类别，你可以这样做：

<CATEGORY NAMES> = 区分大小写的类别名称
<GROUP> = 用户组名称（例如：Staff、Trust_level_0）
<MINIMUM LIKE COUNT> = 你想设置的最小点赞数
<POST COUNT THRESHOLD> = 最小帖子数

SELECT p.user_id, min(p.created_at) granted_at, MIN(p.id) post_id
FROM badge_posts p
JOIN topics t ON t.id = p.topic_id
WHERE t.category_id IN (
        SELECT id FROM categories WHERE name ILIKE ANY (ARRAY['<CATEGORY NAME 1>', '<CATEGORY NAME 2>', '<CATEGORY NAME 3>'])
    ) AND (
        SELECT count(*)
        FROM post_actions pa
        WHERE pa.post_id = p.id
            AND post_action_type_id = (
                SELECT id FROM post_action_types WHERE name_key = 'like'
            ) AND pa.user_id IN (
                SELECT gu.user_id
                FROM group_users gu
                WHERE gu.group_id = ( SELECT id FROM groups WHERE name ILIKE '<GROUP>' )
            )
    ) >= <MINIMUM LIKE COUNT>
    AND p.user_id >= 0
GROUP BY p.user_id
HAVING count(*) >= <POST COUNT THRESHOLD>

[Firepup650 (Firepup Sixfifty)]

嘿 @Lilly！

这两个查询看起来都很棒，但我想看看是否可以将查询应用于所有类别。当我尝试时，我一直收到关于子查询返回多行的错误，所以我来这里请教一下。

[Lilly (Lillian Louis)]

您是指您想要对所有类别使用相同的查询吗？

SELECT p.user_id, min(p.created_at) granted_at, MIN(p.id) post_id
FROM badge_posts p
JOIN topics t ON t.id = p.topic_id
WHERE (
        SELECT count(*)
        FROM post_actions pa
        WHERE pa.post_id = p.id
            AND post_action_type_id = (
                SELECT id FROM post_action_types WHERE name_key = 'like'
            ) AND pa.user_id IN (
                SELECT gu.user_id
                FROM group_users gu
                WHERE gu.group_id = ( SELECT id FROM groups WHERE name ILIKE '<GROUP>' ) 
            )
    ) >= <MINIMUM LIKE COUNT>
    AND p.user_id >= 0
GROUP BY p.user_id
HAVING count(*) >= <POST COUNT THRESHOLD>

[Firepup650 (Firepup Sixfifty)]

这似乎可行，但在您针对的组是 staff 时似乎会失败。我尝试了 Staff 和 staff 作为组名，并将帖子数和点赞数暂时设置为 1，它说不会授予任何徽章。我在这里做错了什么？

[Lilly (Lillian Louis)]

我使用了小写的 staff，对我来说是有效的。

SELECT p.user_id, min(p.created_at) granted_at, MIN(p.id) post_id
FROM badge_posts p
JOIN topics t ON t.id = p.topic_id
WHERE (
        SELECT count(*)
        FROM post_actions pa
        WHERE pa.post_id = p.id
            AND post_action_type_id = (
                SELECT id FROM post_action_types WHERE name_key = 'like'
            ) AND pa.user_id IN (
                SELECT gu.user_id
                FROM group_users gu
                WHERE gu.group_id = ( SELECT id FROM groups WHERE name ILIKE 'staff' ) 
            )
    ) >= 1
    AND p.user_id >= 0
GROUP BY p.user_id
HAVING count(*) >= 1

[Firepup650 (Firepup Sixfifty)]

奇怪 我这边还是不行。我将尝试针对其他几个组运行它，看看是否能找到问题。

编辑：针对另一个组运行了，查询仍然失败。我不确定这里会是什么问题。会不会取决于主组？

编辑 2：那行不通，staff 似乎不能被设置为主组。

[Lilly (Lillian Louis)]

我想我知道为什么了。我吃完晚饭后会帮你处理。我反正需要练习SQL。Badge SQL 比 postgres 更具限制性。我通过了子查询部分。

[JammyDodger]

我还没完全醒过来，我觉得徽章查询需要先喝两杯茶才能完全处理好，但我最近一直在和机器人讨论这类查询，我认为使用实际的 post_action_code_id 和 group_id 代码比使用嵌套的 SELECT 查询来查找相同的东西要好。

[Lilly (Lillian Louis)]

我这样做是为了获取 posts、posts_actions、group_users 和 groups 的必要模式表

SELECT column_name, data_type, character_maximum_length
FROM INFORMATION_SCHEMA.COLUMNS
WHERE table_name = '<TABLE NAME>';

然后用这个来获取所有的 group ID：

SELECT name, id FROM groups ORDER BY name

所以，我包含了所有需要的模式表，并指示 Lola，呃，GPTbot 使用实际的 post_action_code ID 和 group_id 代码。然后，经过一些来回争论和一些修正。我们得到了这个。同样，它似乎在 Data Explorer 中有效，但我仍然无法在 Badge Previewer 中获取任何内容。

G = group_id
X = minimum number of likes
Y = minimum number of posts

SELECT pa.user_id, MIN(pa.post_id) as post_id, COUNT(pa.post_id) as post_count, COUNT(pa.id) as like_count, MAX(pa.created_at) as granted_at
FROM post_actions pa
JOIN group_users gu ON gu.user_id = pa.user_id
WHERE gu.group_id = G AND pa.post_action_type_id = 2
GROUP BY pa.user_id
HAVING COUNT(pa.post_id) >= Y AND COUNT(pa.id) >= X

是的，我给 GP4bot 起名叫 Lola

[JammyDodger]

我叫它 Bert。 尽管我们关系复杂。

我认为另一种限制是使用 MIN(p.created_at) granted_at 会给出他们第一个的日期，而不是，比如说，他们第十个的日期。可以改为 MAX，但如果你针对他们已经拥有超过 10 个的历史数据运行它，也会得到一个“不正确”的日期。

我还在考虑这个问题。

我曾尝试使用 ROW_NUMBER() 取得了一些成功，但还没有具体成果。

[Lilly (Lillian Louis)]

嗯，我同意。有些事情还是感觉不对。我要去睡觉了。

[Lilly (Lillian Louis)]

尽管我对此感到很有趣，而且它正在帮助我重新学习 SQL 以及如何编写更好的查询。拥有 Lola / GPT4bot 作为 SQL 助手很有帮助，但你必须指导她并以正确的方式提问。我试图找到方法让她访问大部分的模式表信息，这样我就不必为我们处理的每个查询问题都这样做。提供表模式信息可以获得更好的结果。我曾尝试给她一个指向 core 中可用模式的链接，但这只会让她在谷歌上瞎逛。

我知道徽章查询预览器正在运行时，我很想和她一起工作。我需要练习 SQL 和进行徽章查询。顺便说一下，她无法修复它，而且她仍然泡的伯爵茶不够热。尽管昨晚的 SQL 课是我多年来最好的约会。

[Firepup650 (Firepup Sixfifty)]

使用该查询，我们似乎遇到了一个奇怪的问题。它似乎只授予了员工，而且我几乎可以肯定一些非员工也符合该条件。这是我弄坏了什么东西，还是查询问题？

[Lilly (Lillian Louis)]

是的，我知道有些地方不对劲，一旦我的实例更新了预览修复程序，我就会着手处理。

[JammyDodger]

为了重新开始，因为我觉得来回沟通让我感到困惑。

这样做的目的是否是为所有类别中至少被@staff点赞过一次的帖子的特定数量授予徽章？

[Firepup650 (Firepup Sixfifty)]

我本来打算将其授予那些在所有类别中拥有 X 篇帖子且获得 Y 个点赞的用户，由工作人员审核。在我的例子中，是 10 篇帖子，5 个点赞。

[JammyDodger]

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

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

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