"Sorry, An error has occurred" when changing a badge sql trigger to "When a user is edited or created"

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.

Is this useful?

1 Like

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 ...
1 Like

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
2 Likes

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
1 Like

Oops, didn’t spot that you added that. Yeah, it’s FROM TABLENAME ALIAS [, TABLENAME ALIAS]...

1 Like

Great, I’ll post this in the thread where I found the code to use by others

Fixed here:

https://github.com/discourse/discourse/commit/381784ea62ded426d268678b01226d49650800c0

3 Likes