How to select records where a 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 the bans for a certain admin where there is no record in the ban_reasons table. How can I do this in Rails without looping through all the ban records and filtering out all the ones 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 it the "rails" way)

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

Answer 1

ActiveRecord only gets you to a point, everything after should be done by raw SQL. The good thing about AR is that it makes it pretty easy to do that kind of stuff.

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

I'd go with raw SQL and here is another query you could try if yours doesn't perform 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 2

Your solution works great (only one request) but it's almost plain SQL:

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

You may also try the following, and let rails do part of the job:

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