# How to query a confirmation-type custom user field?
**URL:** https://meta.discourse.org/t/how-to-query-a-confirmation-type-custom-user-field/68397
**Category:** Support
**Created:** [2017年八月19日 23:12 UTC](https://meta.discourse.org/t/how-to-query-a-confirmation-type-custom-user-field/68397 "2017-08-19T23:12:11Z")
**Posts on this page:** 9
**Page:** 1
### Author:  [@tophee](https://meta.discourse.org/u/tophee)
#### Post date: [2017年八月19日 23:12 UTC](https://meta.discourse.org/t/how-to-query-a-confirmation-type-custom-user-field/68397/1 "2017-08-19T23:12:11Z")
I’m trying get a list of all users that do _not_ have ticked a specific custom user field. I managed to get a list of those who _have_ ticked it, but I can’t seem to do the inverse:
```plaintext
SELECT cf.user_id
FROM user_custom_fields cf
WHERE cf.name like 'user_field_5' AND
cf.value = 'true'
```
For example, `cf.value = 'false'` gives me an empty list, and so does `cf.value = ''` and also `cf.value = 'null'`.
BTW, I found it confusing that the datatype of a confirmation type user field is not boolean but text. I figure it’s to keep the user field table simple with just one data type?
---
### Author:  [@zogstrip](https://meta.discourse.org/u/zogstrip)
#### Post date: [2017年八月20日 10:30 UTC](https://meta.discourse.org/t/how-to-query-a-confirmation-type-custom-user-field/68397/2 "2017-08-20T10:30:51Z")
Boolean custom fields are stored as `"t"` and `"f"` in the database.
> [@tophee](#):
>
> BTW, I found it confusing that the datatype of a confirmation type user field is not boolean but text. I figure it’s to keep the user field table simple with just one data type?
That’s because all custom fields are store as string in the database and are casted to int/boolean/json/array on load.
---
### Author:  [@tophee](https://meta.discourse.org/u/tophee)
#### Post date: [2017年八月20日 10:41 UTC](https://meta.discourse.org/t/how-to-query-a-confirmation-type-custom-user-field/68397/3 "2017-08-20T10:41:13Z")
> [@zogstrip](#):
>
> Boolean custom fields are stored as “t” and “f” in the database.
Aah, will try that when I’m back at my desk. But what I don’t understand then is why `cf.value = 'true'` worked…
---
### Author:  [@zogstrip](https://meta.discourse.org/u/zogstrip)
#### Post date: [2017年八月20日 10:43 UTC](https://meta.discourse.org/t/how-to-query-a-confirmation-type-custom-user-field/68397/4 "2017-08-20T10:43:12Z")
Not sure, maybe the field isn’t properly “_serialized_” and we’re saving in the database `true.to_s`.
---
### Author:  [@tophee](https://meta.discourse.org/u/tophee)
#### Post date: [2017年八月20日 22:18 UTC](https://meta.discourse.org/t/how-to-query-a-confirmation-type-custom-user-field/68397/5 "2017-08-20T22:18:33Z")
> [@zogstrip](#):
>
> Boolean custom fields are stored as “t” and “f” in the database
Unfortunately, this doesn’t work for me:
```
SELECT cf.user_id
FROM user_custom_fields cf
WHERE cf.name like 'user_field_5' AND
cf.value = 'f'
```
It yields zero results.
---
### Author:  [@david](https://meta.discourse.org/u/david)
#### Post date: [2017年八月20日 22:49 UTC](https://meta.discourse.org/t/how-to-query-a-confirmation-type-custom-user-field/68397/6 "2017-08-20T22:49:19Z")
This is because a row is only added to the user\_custom\_fields table **after** a user has changed the value. So what you want to do is
- Go through the users table
- For each user, see if there’s a user\_custom\_fields row for `user_field_5`
- If so, check whether it’s true, otherwise assume it’s false
In my testing, ticking a boolean saves as `true` in the database, while unticking saves as `NULL`
So I think the SQL you want is
```plaintext
SELECT u.id as user_id, cf.value
FROM users u
LEFT JOIN user_custom_fields cf
ON (cf.user_id = u.id and cf.name like 'user_field_5')
WHERE cf.value IS DISTINCT FROM 'true'
```
Note that `IS DISTINCT FROM` is needed rather than `!=`, because it can deal with one of the values being `NULL`.
---
### Author:  [@system](https://meta.discourse.org/u/system)
#### Post date: [2019年八月31日 19:24 UTC](https://meta.discourse.org/t/how-to-query-a-confirmation-type-custom-user-field/68397/7 "2019-08-31T19:24:07Z")
---
### Author:  [@system](https://meta.discourse.org/u/system)
#### Post date: [2023年三月25日 23:57 UTC](https://meta.discourse.org/t/how-to-query-a-confirmation-type-custom-user-field/68397/8 "2023-03-25T23:57:23Z")
---
### Author:  [@Canapin](https://meta.discourse.org/u/Canapin)
#### Post date: [2023年三月26日 00:39 UTC](https://meta.discourse.org/t/how-to-query-a-confirmation-type-custom-user-field/68397/9 "2023-03-26T00:39:11Z")