How do I write a query that removes one badge while awarding another?

Data & reporting
1

Hello,

After some googles and on-page-searches I was unable to find anything about custom badge series. What I mean by this is a series of two or more badges that contain SQL that removes the previous badge in the series and adds the current badge.

So for example I have unlocked “Custom Badge 1” by posting 100 posts, and on my 200th, I unlock “Custom Badge 2” which for business reasons should not share badge space with the previous badge, the “Custom Badge 1” would be programmatically removed from this user.

TL:DR; How to write a query that removes a badge in such a way to run concurrently with a badge awarding query?

1 Like
2

I think it should be possible if the query uses the user_badges table likely using these fields in some manner.

badge_id    integer 
user_id    integer    fkey users 
granted_at    timestamp

As long as “run revocation query daily” is enabled you could have the “first level” badge get revoked if the “next level” badge had been granted.

There may be a brief time between the grant and the revoke where a member might have both, but it would resolve itself within a day at most.

I recommend that you become more familiar with the Discourse database tables before you attempt crafting badge queries. They can be a “foot gun” and there is good reason it is slightly more work to get the interface functional.

2 Likes