Create a report of users including custom fields

Data & reporting
1

I’ve come up with this as an easy way to quickly get user info per group. It has been hacked together from the suggestions of others and from someone who knows SQL way more deeply than I ever will.

It comes in handy if you need to clone a group, apply watching settings to people already in a group, send a group an old fashioned email, find out what is in their custom fields without dumping all the user data, reveal some info to a select group, etc etc.

You can customise this with a nice direct admin link or your custom fields as per the comments in the code.

We don’t seem to have a great repository for Data Explorer Queries here on Meta now. I hope that here is okay.

--[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.name, u.username, e.email
    -- the next line gives you a direct link to the users admin panel
    -- concat('/admin/users/',u.id,'/',u.username) AS admin_access_url
    -- the line below with MAX allows you to pull User Customised Fields, replicate as needed
    -- replace 'name of the field' and "display name" with your target field
    -- MAX(CASE ucf.name WHEN 'name of the field' THEN value END) AS "display name"
    -- *ensure that all fields except the last one have a comma after them*

  FROM users as u
  LEFT JOIN user_custom_fields AS ucf on u.id = ucf.user_id
  RIGHT JOIN e on u.id = e.user_id
  RIGHT JOIN user_fields AS uf on concat('user_field_', uf.id) = ucf.name
  WHERE u.id IN (SELECT user_id FROM included_users)
  GROUP BY u.id, e.email
  ORDER BY u.username asc
5 Likes