Need some Help with my Triggered Custom Badge Query

Support
1

Hi folks,
I’m stuck on my SQL query. My goal / expectation is that it should grant a specific custom badge immediately when a user replies to a particular topic. The thing is I’m halfway there because the Daily “Backfill” job succeeds though the more immediate effect is clearly not working.

Our forum leverages the badge to provide access other categories (with some help from the automation plugin) so the End of Day/Backfill lag is pretty disorientating for the new users and definitely kills some momentum and enthusiasm upon joining up.

I’ve been ignoring the issue for a while I’m ready to break down and ask for some help.

I essentially lifted the query from the excellent post Creating triggered custom badge queries so it will probably look pretty familiar to most people.

SELECT p.user_id, p.id post_id, p.created_at granted_at
FROM badge_posts p
WHERE p.topic_id = 11558
AND (:backfill OR p.id IN (:post_ids) )

or I’ve also tried

SELECT
DISTINCT ON (p.user_id)
p.user_id, p.id post_id, p.created_at granted_at
FROM badge_posts p
WHERE p.topic_id = 11558 
AND(:backfill OR p.id IN (:post_ids) )

Both work for the EOD backfill but lack the promptness I’m expecting.

Here is a screenshot of the settings as currently set on the badge.

Screen Snippet of the bottom Discourse Badge Settings. Run Revocation Query Daily (optional) is Checked. Query targets posts (optional) is checked. The badge is also allowed to be used as a title and granted multiple times - both options are checked. All the badge visibility settings are checked also. The badge's trigger is "When a user acts on post" and the Badge is part of the Getting Started group.
Screen Snippet of the bottom Discourse Badge Settings. Run Revocation Query Daily (optional) is Checked. Query targets posts (optional) is checked. The badge is also allowed to be used as a title and granted multiple times - both options are checked. All the badge visibility settings are checked also. The badge's trigger is "When a user acts on post" and the Badge is part of the Getting Started group.484×597 32.5 KB

When I check the error logs I see some Backfill failure logs from when I first implemented the query but I don’t see any logs regarding the query failing when a user posts.

I’m probably just missing something obvious but I’ve squinted myself blind at this point so hopefully one of you will take pity on me and give me some pointers… or at least point me at some helpful documentation.

Thanks in advance!

1 Like
2

Haven’t tested yet, but I think the trigger should be “When a user edits or creates a post”?

I think the one you have now = it fires on likes, flags, bookmarks, etc., not on post creation.

Edit: tested it now and it works for me :slight_smile: