fefrei proposed a badge idea that grants users a badge when they have at least <POST_COUNT> posts in the category <CATEGORY_NAME> with at least <LIKE_COUNT> likes from users in the group <TEAM_NAME>. Lilly provided a SQL query to implement this badge, which was later modified by JammyDodger to fit the original requirement of granting the badge to users who have X number of posts with Y likes by staff, across all categories.
The final query provided by JammyDodger uses the badge_posts view, which only counts posts from public categories. To include all categories, the query can be modified to use the posts table instead, but this may require additional lines to exclude deleted posts or topics.
Lilly also experimented with using the actual post_action_code_id and group_id code, and developed a query with the help of a SQL assistant (GPT4bot). However, this query still had issues, and JammyDodger eventually provided a revised version that meets the original requirement.
The discussion highlights the complexity of writing badge queries and the importance of testing and refining them to ensure accuracy.
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>
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>
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>
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>
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
我这样做是为了获取 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
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
也许可以试试这个，它在我的实例上运行成功了。
我这边还是不行。我将尝试针对其他几个组运行它，看看是否能找到问题。
