Should I be able to run any Badge Query in Data Explorer?
I want to create a custom Badge Query using “Appreciated” as a starting point. I type the Appreciated query in Data Explorer:
SELECT p.user_id, current_timestamp AS granted_at
FROM posts AS p
WHERE p.like_count >= 1
AND (:backfill OR p.user_id IN (:user_ids))
GROUP by p.user_id
HAVING count(*) > 20
This gives an error:
missing value for :backfill
I vaguely understand that the “:” is a “bind variable” which improves efficiency. I tried removing the bit about backfill but that gives another error.
You will need to leave out the
AND (:backfill OR p.user_id IN (:user_ids)) condition to run the query in the Data Explorer.
:user_ids both expect parameters to be passed to them.
Thanks, it seems to work fine without the AND clause. Can you tell me what the clause does (assuming it gets the parameters passed in), so I can decide whether to keep it?
If your badge is triggered by a user acting on a post, then you need to include this condition. The best description I’ve seen of how the
:user_ids parameters are used in badge queries is in the ‘Triggered badges have 2 extra constraints’ section of Triggered custom badge queries.