SQL query to exclude records that are part of a group

Question

I can't believe this hasn't been answered elsewhere, but I don't seem to know the right words to convey what I'm trying to do. I'm using Ruby/Rails and PostgreSQL.

I have a bunch of Users in the DB that I'm trying to add to a Group based on a name search. I need to return Users that do not belong to a particular Group, but there is a join table as well (UserGroups, with the appropriate FKs).

Is there a simple way to use this configuration to perform this query without having to result to grabbing all the Users from which belong to the group and doing something like .where.not(id: users_in_group.pluck(:id)) (these groups can be pretty huge, so I don't want to send that query to the DB on a text search as the user types).

Answer 1

I need to return Users that do not belong to a particular Group

SELECT *
FROM   users u
WHERE  username ~ 'some pattern'  -- ?
AND    NOT EXISTS (
   SELECT FROM user_groups ug
   WHERE  ug.group_id = 123       -- your group_id to exclude here
   AND    ug.user_id  = u.id 
   );

See:

• Select rows which are not present in other table