I’ve created a new badge which is assigned when a user fill out a custom field. Unfortunately if I set the SQL to trigger when they edit their profile, I get this pop-up. Setting it to query daily works.
I checked for error logs but there seems to be nothing there.
Yep… so here’s how it works. When a user updates their profile, your query is run with the user_ids parameter set to a comma-separated list of all the users who updated your profile, and you’re supposed to filter your query so that only badges/rows for those users are returned/checked.
Your query is also run daily, with the :user_ids parameter unset and :backfill set to true.
The implementation looks like this:
SELECT u.id user_id, current_timestamp granted_at
FROM ..., users u
WHERE ...
AND ...
AND (
u.id IN (:user_ids)
OR :backfill
)
AND ...
SELECT cf.user_id user_id, cf.updated_at granted_at
FROM user_custom_fields cf
WHERE cf.name like 'user_field_1' AND
LENGTH(cf.value) > 1
would it work if I modify it to:
SELECT cf.user_id user_id u.id, cf.updated_at granted_at
FROM user_custom_fields cf u
WHERE cf.name like 'user_field_1'
AND (
u.id IN (:user_ids)
OR :backfill
)
AND LENGTH(cf.value) > 1
SELECT cf.user_id user_id, cf.updated_at granted_at
FROM user_custom_fields cf u
WHERE cf.name = 'user_field_1'
AND (
cf.user_id IN (:user_ids)
OR :backfill
)
AND length(cf.value) > 1
This seems to work (I removed the ‘u’ table from the FROM)
SELECT cf.user_id user_id, cf.updated_at granted_at
FROM user_custom_fields cf
WHERE cf.name = 'user_field_2'
AND (
cf.user_id IN (:user_ids)
OR :backfill
)
AND length(cf.value) > 1