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; Here is what user_custom_fields looks like: [38%20PM] ideally, I want one record, per user, that shows the fields I want plus the values for the rows in user__custom__fields.. Any help figuring out the joins/syntax?

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.

Querying user_custom_fields

Community Building Data & reporting

slackmoehrle (Slack-Moehrle) June 15, 2019, 5:30pm 3

This looks promising. I will work with it and see how I make out. Thank you for taking time out of your day to answer my post.

EDIT: This is the perfect solution. I integrated this to my existing work and things are performing great.

Topic		Replies	Views
Displaying user custom fields in data explorer query results Data & reporting sql-query	5	1723	March 15, 2020
Query to filter users by sign-up 'custom fields' Data & reporting sql-query , user-custom-fields	11	1867	January 17, 2022
Trying to query to find user custom fields filled out Data & reporting sql-query , user-custom-fields	3	1120	May 1, 2018
Adding non-visible user custom fields to the API Site Management rest-api , how-to , user-custom-fields , users	2	1846	October 18, 2023
Report on User Fields? Product Support	3	1205	August 14, 2019

Querying user_custom_fields

Related topics