Custom User Fields: How to query them and why redundant tables

[Marcus]

I em intensively working with custom user fields and have two questions:

1. Redundant are: xf_user_field_value which contains data from xf_user_profile(row custom_fields)

Is there a reason to have the serialized data from xf_user_profile:custom_fields also in xf_user_field_value?
2. How would I best query serialized data? How to find all users who clicked on the pizza-checkbox for both "Pizza Napoli" and "Pizza Salami"?

 

[Chris D]

The field in xf_user_profile is a serialised cache of the data obtained from the values in xf_user_field_value

The values in xf_user_profile are usually unserialized in the $visitor object and probably in most other locations that contains user values, e.g. Posts, profiles etc.

Great for performance as it saves a query that could be pretty heavy depending on the size of the field value table.

I think there's some template helpers and other helpers that can grab the information also.

Failing that if you can't access the values any other way just do a good old normal query on the xf_user_field_value table.

 

[Marcus]

Yeah the problem is I only have one custom checkbox user field. Its data is stored serialized in xf_user_profile. And its data is also stored serialized in xf_user_field.

The planned user field will have around 10.000 different values.

 

[Marcus]

For my reference, xf_user_field_value is the original data

Basically rebuilding the fields on save may not be doable. The other alternative is mostly to prevent you from editing the internal keys after you create a field (like you can't change the ID).

xf_user_field_value is the canonical version. Anything in xf_user_profile is simply for caching and optimization.