How to query a confirmation-type custom user field?

Support

AI-generated summary

The discussion revolves around querying a confirmation-type custom user field in a database. tophee is trying to retrieve a list of users who have not ticked a specific field, but is having trouble with the inverse query. zogstrip explains that boolean custom fields are stored as "t" and "f" in the database, but tophee finds this inconsistent with their experience.

It’s later revealed by david that a row is only added to the user_custom_fields table after a user has changed the value, and that unticking a boolean field saves as NULL in the database. david provides a revised SQL query to achieve the desired result:

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'
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.

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.