Tony Tony - 3 months ago 7x
SQL Question

Inverse of IN in Rails

I feel foolish, but I cannot find the answer to this.

If I have a User with many attributes, given a list of attributes, I can ask rails something like this:

User.where(" IN ?", list_of_attribute_ids)

With the appropriate joins or includes or whatever.

However, I have no idea how to find the inverse set of those users. That is, given 100 users, if the result return 75 entries, I don't know how to find the other 25!

I thought

User.where(" NOT IN ?", list_of_attribute_ids)

might work (similarly, User.where.not), but it doesn't! Instead, it looks for those users where any of their attributes are not one of the list, which is useful, but not what I want.

The only way I know how to do it, is with something like:

User.where.not(id: User.where(" IN ?", list_of_attribute_ids).pluck(:id))

Which is sort of like the SQL for select user where id not in (gather a list of ids).

But this is massively non-performant, and generally just can't cope with a database with more than a few (hundred) entries.

How do you do this?


I think you could use left outer joins, like @Vishal mentioned in the comments.

See the guides:

rails 4:

joins("LEFT OUTER JOIN <something>")

rails 5: