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