# Querying user\_custom\_fields
**URL:** https://meta.discourse.org/t/querying-user-custom-fields/120418
**Category:** Data & reporting
**Tags:** sql-query, user-custom-fields
**Created:** [6월 15, 2019, 12:29오전 UTC](https://meta.discourse.org/t/querying-user-custom-fields/120418 "2019-06-15T00:29:40Z")
**Posts on this page:** 4
**Page:** 1
### Author:  [@slackmoehrle](https://meta.discourse.org/u/slackmoehrle)
#### Post date: [6월 15, 2019, 12:29오전 UTC](https://meta.discourse.org/t/querying-user-custom-fields/120418/1 "2019-06-15T00:29:40Z")
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:
```sql
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:
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?
---
### Author:  [@simon](https://meta.discourse.org/u/simon)
#### Post date: [6월 15, 2019, 4:12오전 UTC](https://meta.discourse.org/t/querying-user-custom-fields/120418/2 "2019-06-15T04:12:24Z")
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:
```sql
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`.
---
### Author:  [@slackmoehrle](https://meta.discourse.org/u/slackmoehrle)
#### Post date: [6월 15, 2019, 5:30오후 UTC](https://meta.discourse.org/t/querying-user-custom-fields/120418/3 "2019-06-15T17:30:06Z")
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.
---
### Author:  [@system](https://meta.discourse.org/u/system)
#### Post date: [7월 15, 2019, 5:30오후 UTC](https://meta.discourse.org/t/querying-user-custom-fields/120418/4 "2019-07-15T17:30:06Z")
This topic was automatically closed 30 days after the last reply. New replies are no longer allowed.