Tony Tony - 4 months ago 17
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("attributes.id 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("attributes.id 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("attributes.id 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?

Answer

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

See the guides: http://guides.rubyonrails.org/active_record_querying.html#left-outer-joins

rails 4:

joins("LEFT OUTER JOIN <something>")

rails 5:

left_outer_joins(:something)