Data Explorer query to get useful group info

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 UCFs 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 UCFs 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 if you wish
    -- concat('https://yoursitename/admin/users/',u.id,'/',u.username) AS admin_access_url
    -- the next line allows you to pull User Customised Fields, replicate as needed for each field
    -- MAX(CASE uf.name WHEN 'ucfname' THEN value END) AS "ucfname"
    -- *ensure that all fields except the last one have a comma after them*

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