Hi @LeoMcA! I love this query of yours that you created last year to use the data explorer to display a chronological list of user topics started, replied and liked. 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! 
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 
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. 
Here we go:
-- [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:
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:
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
Fantabulous. thanks, @LeoMcA! Works perfectly. Really all I want is the wordpress user ID for matching. It’s stored as a custom user field.
我发现这个查询对我们的网站非常有用，但花了不少时间进行定制/修改，使其能按组工作，并包含一些其他用户信息，比如邮箱。
不过，我即将启动一个大型实例，其中包含一些用户字段，这些字段在一段时间内会有些动态变化。
有没有人足够聪明，能重新构建这个查询，使其自动输出所有用户数据字段？也许 @simon 可以？
这是我目前的查询：
–[params]
– string :group_nameWith 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