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 “Мобильный телефон”,
MAX(CASE ucf.name WHEN ‘user_field_2’ THEN value END) AS “Должность”,
MAX(CASE ucf.name WHEN ‘user_field_5’ THEN value END) AS “Работодатель”,
MAX(CASE ucf.name WHEN ‘user_field_4’ THEN value END) AS “Индекс”,
MAX(CASE ucf.name WHEN ‘user_field_14’ THEN value END) AS “Клиническая профессия”,
MAX(CASE ucf.name WHEN ‘user_field_3’ THEN value END) AS “Клиническая специализация”,
MAX(CASE ucf.name WHEN ‘user_field_17’ THEN value END) AS “Работает в клинике”,
MAX(CASE ucf.name WHEN ‘user_field_1’ THEN value END) AS “Интерес к информатике”,
MAX(CASE ucf.name WHEN ‘user_field_15’ THEN value END) AS “Диетические требования”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