I am working on badge SQL that grant’s users a badge when they have all of the badges in a specified list. (E.g. id’s 3, 4 and 5). I know this badge query works for one badge: SELECT user_id, count(id) , current_timestamp granted_at FROM user_badges WHERE badge_id=4 GROUP BY user_id But how would I make this apply to multiple badge id’s? Adding another badge_id = X won’t work because this looks for one badge. Intersect seems like it would work but it isn’t supported. This query would be run daily and wouldn’t target any posts. Thanks!

I use something like this to create Alternative User Level for my members. You can replace ib.badge_id with the badge id your members should have achieved. SELECT user_id, CURRENT_TIMESTAMP granted_at, NULL post_id FROM user_badges pb WHERE badge_id = 1 AND EXISTS ( SELECT 1 FROM user_badges ib WHERE pb.user_id = ib.user_id AND ib.badge_id = 11 ) AND EXISTS ( SELECT 1 FROM user_badges ib WHERE pb.user_id = ib.user_id AND ib.badge_id = 124 ) AND EXISTS ( SELECT 1 FROM user_badges ib WHERE pb.user_id = ib.user_id AND ib.badge_id = 123 ) AND EXISTS ( SELECT 1 FROM user_badges ib WHERE pb.user_id = ib.user_id AND ib.badge_id = 127 ) AND EXISTS ( SELECT 1 FROM user_badges ib WHERE pb.user_id = ib.user_id AND ib.badge_id = 5 )

תג עבור תגים אחרים מסוימים

קהילה נתונים ודיווח

Qursch 17 בדצמבר,‏ 2020,‏ 12:27pm 3

Thanks for your help, that works perfectly!

נושא		תגובות	צפיות
Badges predicated on getting a series of other badges? Data & reporting sql-triggered-badge	4	2598	24 בנובמבר,‏ 2014
Assigned a badge based on multiple badge assignment Data & reporting sql-triggered-badge	8	1234	18 במרץ,‏ 2022
An extra badge based on already granted badges number? Data & reporting sql-triggered-badge	1	739	23 במאי,‏ 2023
Awarding badges multiple times with custom SQL Data & reporting sql-triggered-badge	2	1273	25 באוקטובר,‏ 2017
Grant a badge based on number of bronze/silver/gold badges Data & reporting sql-triggered-badge	0	326	17 בינואר,‏ 2024

תג עבור תגים אחרים מסוימים

נושאים קשורים