Creating Custom Badges and Triggers

data & reporting
1

So I was creating a custom badge query. I already created one successfully and saved it. This one worked without any errors:

SELECT user_id, 0 post_id, current_timestamp granted_at 
FROM badge_posts  
WHERE (:backfill OR user_id IN (:user_ids) OR 0 NOT IN (:post_ids) )
GROUP BY user_id 
HAVING count(*) > 499

I got it from here: Some common badge queries

The trigger I picked for this was “After a post is processed” instead of “When a user edits or creates a post”. If I change the trigger to the latter, it will pop up an error.

I tried to find more information about the different triggers and the only topic I could find is here: Create Triggered Custom Badge Queries

Almost all my badge queries depend on number of posts or replies or creation of topics in a specific category.

I’m trying to understand what is the difference between these 3 triggers and when do you pick it based on the query?

  1. After a post is processed
  2. When a user acts on a post
  3. When a user edits or creates a post

Why did I get an error when choosing #3 as a trigger but not when choosing #1

I tried creating another badge query using this one:

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 'foo'
) AND p.post_number = 1
GROUP BY p.user_id

where ‘foo’ would be the slug for my category. This time, if I leave the trigger blank or pick “Update daily”, it will save without any errors. If I change the trigger to any of the 1-3 above, it will have an error.

I think it’s because the sql query is missing information on how to trigger it based on :post_ids ? If I’m right, how would I fix the above query so it’s not daily but triggers on every post in the foo category?

Any explanation would be appreciated or a reference to where I can read more about triggers.

2 Likes