Displaying user custom fields in data explorer query results


(Tobias Eigen) #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:


(Leo McArdle) #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:


(Tobias Eigen) #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:


(Leo McArdle) #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:


(Tobias Eigen) #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: