How to query a confirmation-type custom user field?

Support
1

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:

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?

2

Boolean custom fields are stored as "t" and "f" in the database.

That’s because all custom fields are store as string in the database and are casted to int/boolean/json/array on load.

2 Likes
3

Aah, will try that when I’m back at my desk. But what I don’t understand then is why cf.value = 'true' worked…

4

Not sure, maybe the field isn’t properly “serialized” and we’re saving in the database true.to_s.

5

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.

6

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

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.

5 Likes