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


(Divided By Zer0) #1

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.


(Kane York) #2

More missing error text. The actual error will show up in your network inspector.


(Divided By Zer0) #3

Sorry, but where is the network inspector?


(Régis Hanol) #4

The one in your browser


(Jeff Atwood) #5

Yeah missing error text is an Ember upgrade regression cc @eviltrout.


(Divided By Zer0) #6

Is this useful?


(Kane York) #7

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

(Divided By Zer0) #8

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)


(Kane York) #9

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

(Divided By Zer0) #10

Awesome, thanks a lot!

EDIT: Oh, it’s telling me there’s an error with the query when trying to run a preview…


(Divided By Zer0) #11

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

(Kane York) #12

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


(Divided By Zer0) #13

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


(Robin Ward) #14

Fixed here:

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


(Jeff Atwood) #15