Displaying user custom fields in data explorer query results

data & reporting
1

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. :pray:

Trust all is well. Happy holidays! :night_with_stars:

5 Likes
2

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! :wink:

Been pretty busy with Uni, but I canā€™t really complain about that - hope youā€™ve had a good holiday season too :slight_smile:

3 Likes
3

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. :rocket:

1 Like
4

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 :wink:

8 Likes
5

Fantabulous. thanks, @LeoMcA! Works perfectly. Really all I want is the wordpress user ID for matching. Itā€™s stored as a custom user field. :rocket:

1 Like
6

Iā€™ve found that query really helpful for our site, but it took a while to customise / hack, make it work by group, and include some other user stuff like emails.

However, Iā€™m about to embark on a large instance with some user fields which will be a bit dynamic for a while.

Does anyone out there have the smarts to re-build this query so that it does automatically spit out all user data fields? Maybe @simon?

Here is my query at the mo:

ā€“[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