# Displaying user custom fields in data explorer query results
**URL:** https://meta.discourse.org/t/displaying-user-custom-fields-in-data-explorer-query-results/76648
**Category:** Data & reporting
**Tags:** sql-query
**Created:** [2017年十二月22日 22:48 UTC](https://meta.discourse.org/t/displaying-user-custom-fields-in-data-explorer-query-results/76648 "2017-12-22T22:48:03Z")
**Posts on this page:** 6
**Page:** 1
### Author:  [@tobiaseigen](https://meta.discourse.org/u/tobiaseigen)
#### Post date: [2017年十二月22日 22:48 UTC](https://meta.discourse.org/t/displaying-user-custom-fields-in-data-explorer-query-results/76648/1 "2017-12-22T22:48:03Z")
Hi @LeoMcA! I love this query of yours that you created last year to use the [data explorer](https://meta.discourse.org/t/32566?silent=true) to display a [chronological list of user topics started, replied and liked](https://meta.discourse.org/t/paid-data-explorer-challenge-chronological-list-of-user-topics-started-replied-and-liked/44407/6). Would you be willing to update the query to also optionally include user custom fields in the results? It would be a big help to me right now. 🙏
Trust all is well. Happy holidays! 🌃
---
### Author:  [@LeoMcA](https://meta.discourse.org/u/LeoMcA)
#### Post date: [2017年十二月30日 11:19 UTC](https://meta.discourse.org/t/displaying-user-custom-fields-in-data-explorer-query-results/76648/2 "2017-12-30T11:19:58Z")
Sure, should be a pretty trivial change.
I should get it done by the end of next week… hope you can wait until next year! 😉
Been pretty busy with Uni, but I can’t really complain about that - hope you’ve had a good holiday season too 🙂
---
### Author:  [@tobiaseigen](https://meta.discourse.org/u/tobiaseigen)
#### Post date: [2017年十二月30日 16:46 UTC](https://meta.discourse.org/t/displaying-user-custom-fields-in-data-explorer-query-results/76648/3 "2017-12-30T16:46:53Z")
That’s great news, thanks Leo! I really appreciate it.
Glad to hear you’re keeping well and busy with school, and enjoying the holidays. All is well on my end. I’m a bit sad to see the end of a nice break from work but am also looking forward to getting back into things at Namati.
I’m working on a dashboard to monitor member activity and get better aggregated statistics on activity across our network, including face to face events, webinars, resource downloads among other things. It’s going to be interesting to be able to include discourse topics started, replied and liked. 🚀
---
### Author:  [@LeoMcA](https://meta.discourse.org/u/LeoMcA)
#### Post date: [2018年一月5日 22:08 UTC](https://meta.discourse.org/t/displaying-user-custom-fields-in-data-explorer-query-results/76648/4 "2018-01-05T22:08:39Z")
Here we go:
```plaintext
-- [params]
-- date :date_from = 1970-01-01
-- date :date_to = 2038-01-19
-- boolean :guess_domain = true
-- string :domain = https://example.com
WITH ss AS (
SELECT CASE
WHEN :guess_domain = true THEN concat('https://', split_part(value, '@', 2))
ELSE :domain
END AS domain
FROM site_settings
WHERE name = 'notification_email'
), cfs AS (
SELECT
u.id AS user_id,
MAX(CASE ucf.name WHEN 'user_field_1' THEN value END) AS first_field,
MAX(CASE ucf.name WHEN 'user_field_2' THEN value END) AS second_field,
MAX(CASE ucf.name WHEN 'user_field_3' THEN value END) AS third_field,
MAX(CASE ucf.name WHEN 'user_field_4' THEN value END) AS fourth_field,
MAX(CASE ucf.name WHEN 'user_field_5' THEN value END) AS fifth_field
FROM user_custom_fields AS ucf
RIGHT JOIN users AS U on ucf.user_id = u.id
GROUP BY u.id
)
SELECT
ua.created_at,
CASE
WHEN ua.action_type = 1 THEN 'Like'
WHEN ua.action_type = 4 THEN 'New Topic'
WHEN ua.action_type = 5 THEN 'Topic Reply'
WHEN ua.action_type = 12 THEN 'Message'
END AS action,
u.username,
ucf.*,
t.title AS topic_title,
c.name AS category,
pc.name AS parent_category,
concat((SELECT domain from ss), '/t/', t.id, '/', (CASE WHEN p.post_number IS NOT NULL then p.post_number ELSE 1 END)) AS url
FROM user_actions AS ua
JOIN users AS u ON ua.user_id = u.id
LEFT JOIN cfs AS ucf on ucf.user_id = u.id
JOIN topics AS t ON ua.target_topic_id = t.id
LEFT JOIN posts AS p ON ua.target_post_id = p.id
LEFT JOIN categories AS c ON t.category_id = c.id
LEFT JOIN categories AS pc ON c.parent_category_id = pc.id
WHERE
ua.user_id != -1
AND
ua.action_type IN (1, 4, 5, 12)
AND
ua.created_at BETWEEN :date_from AND :date_to
ORDER BY created_at DESC
```
It’ll require a little bit of modification to your exact setup. Add or remove the series of 5 lines which look like this:
```plaintext
MAX(CASE ucf.name WHEN 'user_field_1' THEN value END) AS first_field
```
to your exact number of custom fields (if you need more than 5, then add a line with `user_field_6`, `user_field_7`, etc.). You can also change `first_field`/`second_field`/etc. to a more descriptive name of what the field contains.
So that section, based on what I remember of your custom user fields, might end up looking something like:
```plaintext
SELECT
u.id AS user_id,
MAX(CASE ucf.name WHEN 'user_field_1' THEN value END) AS facebook,
MAX(CASE ucf.name WHEN 'user_field_2' THEN value END) AS twitter,
MAX(CASE ucf.name WHEN 'user_field_3' THEN value END) AS linked_in
FROM user_custom_fields AS ucf
RIGHT JOIN users AS U on ucf.user_id = u.id
GROUP BY u.id
```
There’s _probably_ a way to do this automatically, but I’m not enough of a SQL guru to work it out 😉
---
### Author:  [@tobiaseigen](https://meta.discourse.org/u/tobiaseigen)
#### Post date: [2018年一月6日 00:06 UTC](https://meta.discourse.org/t/displaying-user-custom-fields-in-data-explorer-query-results/76648/5 "2018-01-06T00:06:19Z")
Fantabulous. thanks, @LeoMcA! Works perfectly. Really all I want is the wordpress user ID for matching. It’s stored as a custom user field. 🚀
---
### Author:  [@nathank](https://meta.discourse.org/u/nathank)
#### Post date: [2020年三月15日 10:59 UTC](https://meta.discourse.org/t/displaying-user-custom-fields-in-data-explorer-query-results/76648/6 "2020-03-15T10:59:46Z")
> [@LeoMcA](#):
>
> _可能_有办法自动完成这件事，但我还不足以成为 SQL 专家来搞定它 😉
我发现这个查询对我们的网站非常有用，但花了不少时间进行定制/修改，使其能按组工作，并包含一些其他用户信息，比如邮箱。
不过，我即将启动一个大型实例，其中包含一些用户字段，这些字段在一段时间内会有些动态变化。
有没有人足够聪明，能重新构建这个查询，使其自动输出所有用户数据字段？也许 @simon 可以？
这是我目前的查询：
> –[params]
> – string :group\_name
>
> With included\_users AS (
> SELECT
> gu.user\_id
> FROM group\_users gu
> JOIN groups g
> ON g.id = gu.group\_id
> WHERE g.name = :group\_name
> ),
> e AS (
> SELECT email, user\_id
> FROM user\_emails u
> WHERE u.primary = true
> )
>
> SELECT
> u.id AS user\_id, u.username, u.name, e.email,
> MAX(CASE ucf.name WHEN ‘user\_field\_6’ THEN value END) AS “Mobile Phone”,
> MAX(CASE ucf.name WHEN ‘user\_field\_2’ THEN value END) AS “Job Title”,
> MAX(CASE ucf.name WHEN ‘user\_field\_5’ THEN value END) AS “Employer”,
> MAX(CASE ucf.name WHEN ‘user\_field\_4’ THEN value END) AS “Postcode”,
> MAX(CASE ucf.name WHEN ‘user\_field\_14’ THEN value END) AS “Clinical Profession”,
> MAX(CASE ucf.name WHEN ‘user\_field\_3’ THEN value END) AS “Clinical Speciality”,
> MAX(CASE ucf.name WHEN ‘user\_field\_17’ THEN value END) AS “Working Clinically”,
> MAX(CASE ucf.name WHEN ‘user\_field\_1’ THEN value END) AS “Informatics Interest”,
> MAX(CASE ucf.name WHEN ‘user\_field\_15’ THEN value END) AS “Dietary Requirement”
>
> FROM user\_custom\_fields AS ucf
> RIGHT JOIN users AS U on ucf.user\_id = u.id
> RIGHT JOIN e on ucf.user\_id = e.user\_id
> WHERE u.id IN (SELECT user\_id FROM included\_users)
> GROUP BY u.id, e.email
> ORDER BY username asc