What I find super exciting about it is that there are zero plugins needed, I crafted it using our badges UI. Not only does it grant the badge daily, it will also grant it after one of the team “likes” a bug.
It runs the following badge query:
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
JOIN post_actions pa ON 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 'discourse' )
)
WHERE category_id = (
SELECT id FROM categories WHERE name ilike 'bug'
) AND p.post_number = 1
GROUP BY p.user_id
The UI is still a tiny bit rough, but I find it super duper cool that the badge system is this extensible. Will knock up a silver version of this badge tomorrow and call it tester
Could you please post this query?
Can I select more teams? Like ‘discourse’ or ‘admins’ or ‘dev_team’
Last question, I can’t use markdown into Badge Description, how do you have created the link to discourse team?
Let me resurrect this thread again: @sam, could you post the query, please? This is interesting for a lot of different use cases, and my SQL has gotten quite rusty
Yes, I did! I was referring to the silver version (requiring a specific number of likes), although my reply for some reason isn’t tagged as a reply to that specific post. Sorry for the confusion.
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>
My untested hypothesis is that the only thing here that is specific to base posts is this:
AND p.post_number = 1
Depending on what you want, deleting this clause or making this p.post_number > 1 should work. Again, this is untested, so please be sure to run a plausibility test and report back
I’m trying to use this but getting no badges to be assigned when I run it
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
JOIN post_actions pa ON 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 'Radiant%' )
)
WHERE category_id = (
SELECT id FROM categories WHERE name ilike '[[:<:]]bugs[[:>:]]'
) AND p.post_number = 1
GROUP BY p.user_id
