How to query a confirmation-type custom user field?

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

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.


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

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

Unfortunately, this doesn’t work for me:

SELECT cf.user_id
FROM user_custom_fields cf
WHERE like 'user_field_5' AND
cf.value = 'f'

It yields zero results.

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 as user_id, cf.value
FROM users u
LEFT JOIN user_custom_fields cf 
ON (cf.user_id = and 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.