A user, db0, encountered an error when changing a badge SQL trigger to “When a user is edited or created”. The error message was incomplete, but riking suggested checking the network inspector for the actual error.
db0 was unfamiliar with the network inspector, which zogstrip clarified was a browser tool. codinghorror noted that the incomplete error message was an Ember upgrade regression.
riking explained that the SQL query should filter results based on the user_ids parameter when a user updates their profile, and provided an example of how this could be implemented.
db0 shared their existing query and asked how to modify it to work with the user_ids parameter. riking provided a revised query, and db0 eventually found a working solution by removing an unnecessary table alias.
The issue was later fixed by eviltrout in a commit to the Discourse repository.
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.
More missing error text. The actual error will show up in your network inspector.
Sorry, but where is the network inspector?
The one in your browser
Yeah missing error text is an Ember upgrade regression cc @eviltrout.
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 ...
OK so I have this query:
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
?
(Sorry I’m not very good with SQL)
Almost.
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
Awesome, thanks a lot!
EDIT: Oh, it’s telling me there’s an error with the query when trying to run a preview…
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
Oops, didn’t spot that you added that. Yeah, it’s FROM TABLENAME ALIAS [, TABLENAME ALIAS]...
Great, I’ll post this in the thread where I found the code to use by others