You could try using WITH queries to define temporary tables for your user custom fields. As an example, I have User Fields for phone number and address. I know that the phone number field is user_field_1
in my database and address is user_field_2
. Here’s a query that will return the user’s email address, phone number, and street address, with one row for each user:
WITH user_field_1 AS (
SELECT ucf.value,
ucf.user_id
FROM user_custom_fields ucf
WHERE ucf.name = 'user_field_1'
),
user_field_2 AS (
SELECT ucf.value,
ucf.user_id
FROM user_custom_fields ucf
WHERE ucf.name = 'user_field_2'
)
SELECT
u.id AS user_id,
ue.email,
uf1.value AS phone_number,
uf2.value AS address
FROM users u
LEFT JOIN user_field_1 uf1
ON uf1.user_id = u.id
LEFT JOIN user_field_2 uf2
ON uf2.user_id = u.id
JOIN user_emails ue
ON ue.user_id = u.id
The easiest way I know of to find the name
value of your user fields is to view the json of your user fields page (/admin/customize/user_fields.json
). You’ll see the id
for each field in the json data. A field with the id
of 1 creates a user_custom_field
with the name user_field_1
. A field with the id
of 2 creates a user_custom_field
with the name user_field_2
.