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 idea
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: 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?
- After a post is processed
- When a user acts on a post
- 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.