# Badges based on User Custom Fields?
**URL:** https://meta.discourse.org/t/badges-based-on-user-custom-fields/276719
**Category:** Data & reporting
**Tags:** sql-triggered-badge, user-custom-fields
**Created:** [February 18, 2015, 8:47pm UTC](https://meta.discourse.org/t/badges-based-on-user-custom-fields/276719 "2015-02-18T20:47:17Z")
**Posts on this page:** 20
**Page:** 1
### Author:  [@downey](https://meta.discourse.org/u/downey)
#### Post date: [February 18, 2015, 8:47pm UTC](https://meta.discourse.org/t/badges-based-on-user-custom-fields/276719/1 "2015-02-18T20:47:17Z")
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:
```sql
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.
---
### Author:  [@lisajill](https://meta.discourse.org/u/lisajill)
#### Post date: [February 19, 2015, 4:43pm UTC](https://meta.discourse.org/t/badges-based-on-user-custom-fields/276719/2 "2015-02-19T16:43:34Z")
> [@downey](#):
>
> 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:
Awesome badge. Thank you for sharing this!
---
### Author:  [@tobiaseigen](https://meta.discourse.org/u/tobiaseigen)
#### Post date: [February 21, 2015, 6:22pm UTC](https://meta.discourse.org/t/badges-based-on-user-custom-fields/276719/3 "2015-02-21T18:22:49Z")
I 💗 this - thanks for sharing! Works for me.
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.
---
### Author:  [@downey](https://meta.discourse.org/u/downey)
#### Post date: [February 21, 2015, 9:03pm UTC](https://meta.discourse.org/t/badges-based-on-user-custom-fields/276719/4 "2015-02-21T21:03:39Z")
I think you’d simply add the following:
```sql
AND EXISTS (
SELECT 1 FROM user_badges ib
WHERE cf.user_id = ib.user_id
AND ib.badge_id = 888
)
```
Where **888** is the badge ID number of the other prerequisite badge. See the following for details:
> [@Badges predicated on getting a series of other badges?](https://meta.discourse.org/t/badges-predicated-on-getting-a-series-of-other-badges/22471):
>
> Story: As an admin, I’d like to create a badge that is automatically granted if (and only if) a user has received a certain list of other badges, so that we can create a list of accomplishments/tasks in our community, and a title/role when certain of those items are achieved. Is this possible within the current system and SQL, or does there need to be some notion of “sub-badges” to accomplish something like this?
---
### Author:  [@PJH](https://meta.discourse.org/u/PJH)
#### Post date: [February 22, 2015, 5:48pm UTC](https://meta.discourse.org/t/badges-based-on-user-custom-fields/276719/5 "2015-02-22T17:48:12Z")
> [@downey](#):
>
> 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.
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.
---
### Author:  [@downey](https://meta.discourse.org/u/downey)
#### Post date: [February 22, 2015, 7:00pm UTC](https://meta.discourse.org/t/badges-based-on-user-custom-fields/276719/6 "2015-02-22T19:00:31Z")
> [@PJH](#):
>
> And you’ll have to be careful when editing (specifically adding and removing other) fields of course.
Agreed. I’m hopeful to find an answer related to that here:
> [@Unused TOS custom user field safe to delete?](https://meta.discourse.org/t/unused-tos-custom-user-field-safe-to-delete/25464):
>
> AFAIK, I am not using this pre-provided “TOS” custom user field, and we use SSO for external registration. Is it safe to delete this custom field using the (enabled) Delete button? Will doing so renumber/rename my 2nd custom field named user\_field\_2 in the databse or will user\_field\_1 be forever gone and not reused?
---
### Author:  [@db0](https://meta.discourse.org/u/db0)
#### Post date: [May 21, 2015, 11:14am UTC](https://meta.discourse.org/t/badges-based-on-user-custom-fields/276719/7 "2015-05-21T11:14:26Z")
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
```
---
### Author:  [@db0](https://meta.discourse.org/u/db0)
#### Post date: [May 22, 2015, 9:36am UTC](https://meta.discourse.org/t/badges-based-on-user-custom-fields/276719/8 "2015-05-22T09:36:47Z")
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?
Thanks in advance 😄
---
### Author:  [@PJH](https://meta.discourse.org/u/PJH)
#### Post date: [May 25, 2015, 2:38pm UTC](https://meta.discourse.org/t/badges-based-on-user-custom-fields/276719/9 "2015-05-25T14:38:11Z")
> [@db0](#):
>
> 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?
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](https://meta.discourse.org/t/what-cool-badge-queries-have-you-come-up-with/18978/14) futher up for an example)
---
### Author:  [@db0](https://meta.discourse.org/u/db0)
#### Post date: [May 25, 2015, 2:51pm UTC](https://meta.discourse.org/t/badges-based-on-user-custom-fields/276719/10 "2015-05-25T14:51:40Z")
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?
---
### Author:  [@riking](https://meta.discourse.org/u/riking)
#### Post date: [May 25, 2015, 11:29pm UTC](https://meta.discourse.org/t/badges-based-on-user-custom-fields/276719/11 "2015-05-25T23:29:17Z")
Yes, that query should be marked as targets posts, show post on the public badges page, and trigger on post creation.
---
### Author:  [@db0](https://meta.discourse.org/u/db0)
#### Post date: [May 26, 2015, 7:42am UTC](https://meta.discourse.org/t/badges-based-on-user-custom-fields/276719/12 "2015-05-26T07:42:19Z")
Thanks. Can you tell if the same query also grant the badge multiple times if the appropriate checkbox is ticket?
---
### Author:  [@PJH](https://meta.discourse.org/u/PJH)
#### Post date: [May 26, 2015, 12:28pm UTC](https://meta.discourse.org/t/badges-based-on-user-custom-fields/276719/13 "2015-05-26T12:28:58Z")
> [@db0](#):
>
> Can you tell if the same query also grant the badge multiple times if the appropriate checkbox is ticket?
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.
---
### Author:  [@db0](https://meta.discourse.org/u/db0)
#### Post date: [May 26, 2015, 12:41pm UTC](https://meta.discourse.org/t/badges-based-on-user-custom-fields/276719/14 "2015-05-26T12:41:15Z")
> [@PJH](#):
>
> 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
---
### Author:  [@PJH](https://meta.discourse.org/u/PJH)
#### Post date: [May 26, 2015, 1:21pm UTC](https://meta.discourse.org/t/badges-based-on-user-custom-fields/276719/15 "2015-05-26T13:21:58Z")
> [@db0](#):
>
> Do you think I can safely remove it?
Yes. It’s the difference between (for example):
```sql
[pjh@sofa ~]$ psql -d discourse -c "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 = 1000
> LIMIT 20"
user_id | post_id | granted_at
---------+---------+----------------------------
-1 | 34753 | 2014-07-19 17:21:48.438813
4 | 52265 | 2014-08-08 01:16:23.26294
6 | 186028 | 2015-01-02 18:05:22.61456
15 | 63069 | 2014-08-21 20:21:46.150202
16 | 31387 | 2014-07-16 12:51:37.874941
17 | 80603 | 2014-09-13 00:25:50.532543
18 | 120362 | 2014-10-15 13:47:01.920886
20 | 12967 | 2014-06-19 15:49:18.492014
28 | 13208 | 2014-06-19 20:36:59.371834
30 | 35624 | 2014-07-21 17:28:57.3986
33 | 89920 | 2014-09-21 14:20:44.817275
43 | 13300 | 2014-06-19 21:18:48.065129
50 | 143837 | 2014-11-06 05:17:54.776199
60 | 154883 | 2014-11-18 21:49:56.792488
69 | 428292 | 2015-05-22 17:21:00.939645
86 | 14841 | 2014-06-20 22:46:50.493649
110 | 120917 | 2014-10-15 19:32:08.440928
123 | 51260 | 2014-08-07 03:31:05.654638
183 | 160245 | 2014-11-25 21:12:52.132026
261 | 187712 | 2015-01-05 21:24:05.746039
(20 rows)
```
```plaintext
[pjh@sofa ~]$ psql -d discourse -c "SELECT
p.user_id, p.id post_id, p.created_at granted_at
FROM badge_posts p
WHERE p.topic_id = 1000
LIMIT 20"
user_id | post_id | granted_at
---------+---------+----------------------------
589 | 195565 | 2015-01-13 16:11:05.705177
294 | 430986 | 2015-05-25 17:01:07.148368
606 | 430988 | 2015-05-25 17:01:13.176588
606 | 430990 | 2015-05-25 17:01:40.033308
762 | 37144 | 2014-07-22 19:04:02.591978
579 | 430599 | 2015-05-25 14:33:06.928209
294 | 430989 | 2015-05-25 17:01:32.901402
922 | 430109 | 2015-05-25 00:28:35.562221
922 | 430027 | 2015-05-24 21:57:25.817651
606 | 430111 | 2015-05-25 00:29:11.922553
606 | 430026 | 2015-05-24 21:56:50.236247
579 | 430025 | 2015-05-24 21:56:46.181157
922 | 418379 | 2015-05-14 07:41:03.747217
123 | 209061 | 2015-01-26 03:49:34.27203
294 | 430991 | 2015-05-25 17:02:00.277703
762 | 125242 | 2014-10-19 16:24:21.902349
606 | 145216 | 2014-11-07 14:42:28.916019
922 | 431105 | 2015-05-25 20:30:49.410084
294 | 431106 | 2015-05-25 20:31:18.747891
294 | 431103 | 2015-05-25 20:30:45.923232
(20 rows)
[pjh@sofa ~]$
```
Note the repetition of user 922 in the second query - that’s what’s required to give more than one badge.
The first appears to pick (semi) random post numbers to apply badges to
---
### Author:  [@OnceWas](https://meta.discourse.org/u/OnceWas)
#### Post date: [October 15, 2015, 11:22pm UTC](https://meta.discourse.org/t/badges-based-on-user-custom-fields/276719/16 "2015-10-15T23:22:51Z")
If you want your query to not be dependent on system field labels such as `user_field_2`, you can do the following:
```plaintext
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.
---
### Author:  [@paulrudy](https://meta.discourse.org/u/paulrudy)
#### Post date: [November 13, 2020, 5:46pm UTC](https://meta.discourse.org/t/badges-based-on-user-custom-fields/276719/17 "2020-11-13T17:46:52Z")
> [@tobiaseigen](#):
>
> I found it by looking at the source of that page.
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.
---
### Author:  [@paulrudy](https://meta.discourse.org/u/paulrudy)
#### Post date: [November 13, 2020, 6:13pm UTC](https://meta.discourse.org/t/badges-based-on-user-custom-fields/276719/18 "2020-11-13T18:13:59Z")
> [@OnceWas](#):
>
> ```plaintext
> 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
>
> ```
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.
---
### Author:  [@tobiaseigen](https://meta.discourse.org/u/tobiaseigen)
#### Post date: [November 13, 2020, 6:18pm UTC](https://meta.discourse.org/t/badges-based-on-user-custom-fields/276719/19 "2020-11-13T18:18:30Z")
You’re right! I am not immediately finding the custom user field id now myself. You might try with a [data explorer](https://meta.discourse.org/t/32566?silent=true) query.
---
### Author:  [@paulrudy](https://meta.discourse.org/u/paulrudy)
#### Post date: [November 13, 2020, 6:22pm UTC](https://meta.discourse.org/t/badges-based-on-user-custom-fields/276719/20 "2020-11-13T18:22:17Z")
Thanks—I’m not familiar with those, but I’ll look it up here.
I also found @OnceWas’s post that would allow for looking up the custom user field by name, but [ran into an error](https://meta.discourse.org/t/what-cool-badge-queries-have-you-come-up-with/18978/204) that I don’t know how to circumvent.
[Next page](https://meta.discourse.org/t/badges-based-on-user-custom-fields/276719.md?page=2)