Has anyone come up with any badges based on some value for custom user fields? If so, how can we get at those custom user fields with a SQL query?
Here’s a sample badge (there is surely better SQL to do this!) that we quickly hacked together to look for people who filled out an optional custom user field, in this example, to list their GitHub User ID:
SELECT cf.user_id user_id, cf.updated_at granted_at
FROM user_custom_fields cf
WHERE cf.name like 'user_field_2' AND
LENGTH(cf.value) > 1
Note that the value of name field is actually user_field_1, user_field_2, etc., and not the name that you’ve assigned in the admin section.
One thing I noticed: it’s not immediately apparent on the custom user field which user_field_n goes which which user field and it doesn’t start at 1! I found it by looking at the source of that page.
Do you know how to modify this query to display it only when a user has another badge already? I’d like to display a “Tech Ambassador” badge on my site to people but only to those users who have self-identified themselves as such and have also provided a bio.
Well the name given should be in a table somewhere, though joining on it to create a dynamically generated field name may not make the query more self documenting (if that’s even possible in postgres)…
And you’ll have to be careful when editing (specifically adding and removing other) fields of course.
I had an issue with setting that to work on user edit or creation so I had to modify it a bit. If someone wants to do the same, you need to use the following code:
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
Is there a way to modify the above code so that the badge links to the post which granted the badge, similar to how “First Share” works?
Also, if I set that badge to be allowed to be given more than once, will the above code do it for multiple posts in the same thread? If not, how could I do that?
There is no post associated with filling out the custom fields, unless I’m missing something from your query.
But in general, to associate posts you need to tick the Query targets posts checkbox and include a posts.id (or post_id if it’s a foreign key) aliased to post_id and if you use the When a user edits or creates a post trigger you’ll need to include :backfill and :post_ids in the query (see this post futher up for an example)
Gah, you’re right, sorry I referred the wrong code.
I got the following
SELECT
DISTINCT ON (p.user_id)
p.user_id, p.id post_id, p.created_at granted_at
FROM badge_posts p
WHERE p.topic_id = 110 AND
(:backfill OR p.id IN (:post_ids) )
which I took from another tip in the thread. This automatically grants a user who posts in a specific post a badge. Would ticking “Query Target posts” link that to the badge?
Yes, if that box is checked then multiple badges would be awarded.
May I ask why you have the DISTINCT ON (p.user_id) clause in there? If you’re after multiple badges that runs the risk of some of them not being assigned.
I merely copied it from a post in here for assigning badges for on posting on a topic. It was this part which made me wonder if it can assign multiple badges. Do you think I can safely remove it?
EDIT: Just removed it and it seems to work fine. Thanks
If you want your query to not be dependent on system field labels such as user_field_2, you can do the following:
SELECT cf.user_id user_id, cf.updated_at granted_at
FROM user_custom_fields cf
INNER JOIN user_fields uf
ON uf.id = (0 || regexp_replace(cf.name, 'user_field_', ''))::integer
AND uf.name = 'FriendlyFieldName'
WHERE LENGTH(cf.value) > 0
It seems like you’d want to check for a length value greater than zero, just in case you have some single-digit UID GitHub graybeards. And if you are granting a badge multiple times, you’ll want to add GROUP BY cf.user_id to be safe.
We have a dev, a staging, and a live site, and the custom user fields we manually add don’t always have the same user_field_n values from site to site.
I’m wondering which page you were referring to (assuming this info is still up to date)? I’m attempting to find user_field_n, but examining page source for Admin > Customize > User Fields, as well as in user profiles, turns up nothing.
I’m attempting to use this code, and I’m getting the following response: ERROR: invalid input syntax for type integer: "0show_quick_messages", referring to the user settings for the Quick Messages plugin, I’m guessing (even though it’s now disabled).
I’d be grateful for any suggestions about how to avoid or bypass this error.
this - thanks for sharing! Works for me.