How to choose records where the child does not exist

Question

In rails I have 2 tables:

bans(ban_id, admin_id) ban_reasons(ban_reason_id, ban_id, reason_id) 

I want to find all bans for a specific administrator where there is no entry in the ban_reasons table. How can I do this in Rails without looping all the ban entries and filtering all of them with ban.ban_reasons.nil? I want to do this (hopefully) using a single SQL statement.

I just need to do: (But I want to do this "rails").

 SELECT bans.* FROM bans WHERE admin_id=1234 AND ban_id NOT IN (SELECT ban_id FROM ban_reasons) 

Answer 1

Your solution works fine (only one query), but it's almost plain SQL:

 bans = Ban.where("bans.id NOT IN (SELECT ban_id from ban_reason)") 

You can also try the following, and let the rails do part of the task:

 bans = Ban.where("bans.id NOT IN (?)", BanReason.select(:ban_id).map(&:ban_id).uniq) 

Answer 2

ActiveRecord only brings you to the point, everything after that should be done using raw SQL. The good thing about AR is that it does such things pretty easily.

However, since Rails 3 you can do almost everything with the AREL API, although the original SQL may or may not look more readable.

I would go with raw SQL, and here is another query that you could try if yours doesn't work well:

 SELECT b.* FROM bans b LEFT JOIN ban_reason br on b.ban_id = br.ban_id WHERE br.ban_reason_id IS NULL 

Answer 3

Using Where gem exists (which I am the author):

 Ban.where(admin_id: 123).where_not_exists(:ban_reasons)