Custom user fields and their values not related by ID in database


(Christopher Heald) #1

I have been trying to access custom user field values directly from the database, and I noticed that the record IDs for the custom user fields (user_custom_fields.id) table do not relate to to the user field definitions (user_fields.id) table. It looks like the relationship between the tables is via the trailing digit(s) in user_custom_fields.name.

For example:

select id, name, field_type from user_fields;

 id |         name          | field_type 
----+-----------------------+------------
  1 | Real Estate Agent     | confirm
  3 | Website               | text
  4 | Broker Account        | confirm
  6 | Address               | text
  7 | City                  | text
  2 | Real Estate Brokerage | text
  5 | Email                 | text
  8 | Province/State        | text
(8 rows)

select id, user_id, name, value from user_custom_fields;

 id | user_id |     name     |           value           
----+---------+--------------+---------------------------
  1 |       6 | user_field_1 | true
  2 |       6 | user_field_3 | www.richardshomes.com
  3 |       6 | user_field_4 | 
  4 |       6 | user_field_6 | 123 Main Street
  5 |       6 | user_field_7 | Mainsville
  6 |       6 | user_field_2 | XYZ Real Estate
  7 |       6 | user_field_5 | richard@richardshomes.com
  8 |       6 | user_field_8 | BC
(8 rows)

If you run the following query, you can see the disconnect between the ID fields. You would expect the query to return value for city, but you get the value for email, because the city field’s ID is 7 in the field definitions table, but is stored as ID 5 in the custom user field values table.

select ucf.value from user_custom_fields ucf
left join user_fields uf on uf.id = ucf.id
where ucf.user_id = 6 and uf.name = 'City';

           value           
---------------------------
 richard@richardshomes.com
(1 row)

It appears that the link between the custom user field values and the field definitions is made via the field name. In this example, city’s field name is user_field_7.

Ideally, the fix would be to have the IDs between user_custom_fields and user_fields be related. I’m guessing that fixing this would require adding a new ‘field_id’ column to user_custom_fields in order to not break any existing relationships.

This odd non-relationship doesn’t break anything, but it complicates any work with queries that target custom user field data.


Allow user to choose group on registration page
(Christopher Heald) #2

If anybody else has encountered this, here’s the query I use to relate custom user field values and their names:

SELECT ucf.value FROM user_custom_fields ucf
LEFT JOIN user_fields uf ON uf.id = (0 || regexp_replace(ucf.name, 'user_field_', ''))::integer 
WHERE ucf.user_id = $userid AND uf.name = '$fieldname'

(Kane York) #3

You should probably create a view that exposes the CONCAT('user_field_', id::string) calculation as a column on user_fields. Not all user_custom_fields entries have to conform to that format.


(Robin Ward) #4

I agree it’s not ideal. I was the one who did it this way and it was so I could leverage our existing custom fields as storage.

If I do a second pass I’d add the user fields as NULLable fields, but there currently is no pressing need.