The discussion revolves around querying user custom fields in Discourse. slackmoehrle is trying to retrieve user data with custom fields, but is having trouble due to multiple rows per user in the user_custom_fields table.
slackmoehrle provides their current query and an example of the user_custom_fields table. simon suggests using WITH queries to define temporary tables for the user custom fields. He provides an example query that returns the user’s email address, phone number, and street address with one row per user.
slackmoehrle finds simon’s solution promising and later confirms that it works perfectly when integrated with their existing work.
The solution involves defining temporary tables for each custom field using WITH queries and then joining these tables with the users table to retrieve the desired data. The name value of the user fields can be found by viewing the JSON data of the user fields page (/admin/customize/user_fields.json).
Our marketing department wants some data. I can query discourse to get it. However, I am having some trouble with user_custom_fields: We have several custom fields and each one seems to be a row in the table per user. So I may have 6-7 rows per user. I’m working on a join but I start to get many rows per record so I need to figure out how to query properly.
Here is what I have so far:
SELECT
u.id,
u.username_lower AS "username",
u.created_at,
u.last_seen_at,
u.ip_address,
ue.email,
(SELECT COUNT(*)
FROM user_badges ub
WHERE ub.user_id = u.id
) AS badge_count
FROM users u
LEFT OUTER JOIN user_emails ue on u.id = ue.user_id
LEFT OUTER JOIN user_custom_fields ucf on u.id = ue.user_id
WHERE u.active = true
AND u.username_lower='slackmoehrle'
ORDER BY u.id;
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.
