• This site uses cookies. By continuing to use this site, you are agreeing to our use of cookies. Learn more.

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

Marcus

Well-known member
#1
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"?
 
Last edited:

Chris D

XenForo developer
Staff member
#2
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

Well-known member
#3
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

Well-known member
#4
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.