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.
He encontrado esa consulta muy útil para nuestro sitio, pero llevó un tiempo personalizarla / modificarla para que funcionara por grupo e incluir otros datos de usuario como los correos electrónicos.
Sin embargo, estoy a punto de embarcarme en una instancia grande con algunos campos de usuario que serán un poco dinámicos durante un tiempo.
¿Hay alguien por ahí con la capacidad para reconstruir esta consulta de modo que genere automáticamente todos los campos de datos de usuario? ¿Quizás @simon?
Aquí está mi consulta por ahora:
–[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 “Teléfono móvil”,
MAX(CASE ucf.name WHEN ‘user_field_2’ THEN value END) AS “Cargo”,
MAX(CASE ucf.name WHEN ‘user_field_5’ THEN value END) AS “Empleador”,
MAX(CASE ucf.name WHEN ‘user_field_4’ THEN value END) AS “Código postal”,
MAX(CASE ucf.name WHEN ‘user_field_14’ THEN value END) AS “Profesión clínica”,
MAX(CASE ucf.name WHEN ‘user_field_3’ THEN value END) AS “Especialidad clínica”,
MAX(CASE ucf.name WHEN ‘user_field_17’ THEN value END) AS “Trabajando clínicamente”,
MAX(CASE ucf.name WHEN ‘user_field_1’ THEN value END) AS “Interés en informática”,
MAX(CASE ucf.name WHEN ‘user_field_15’ THEN value END) AS “Requisito dietético”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