Queries: All Users Not in Group; All Users in Group

This query returns all users who are not in the group “group-of-users”.

To use this query, just change the “group-of-users” to the slug of the group. You’ll get back a list of usernames of the people who are not in the “group-of-users” group

All users not in group

-- [params]
-- string :group_name = group-of-users

WITH group_members AS(
SELECT
u.username, u.id AS user_id, g.name
FROM users u
JOIN group_users gu
ON gu.user_id = u.id
JOIN groups g
ON g.id = gu.group_id
WHERE g.name = :group_name
), 
all_users AS(
SELECT
    u.username
FROM users u
)
SELECT all_users.username
FROM all_users
EXCEPT
SELECT group_members.username
FROM group_members

And for completion, here is a query to get all users in a group “group-of-users”

All users in Group

-- [params]
-- string :group_name = group-of-users

SELECT
u.username, u.id AS user_id, g.name
FROM users u
JOIN group_users gu
ON gu.user_id = u.id
JOIN groups g
ON g.id = gu.group_id
WHERE g.name = :group_name