How to find rows where no row in associated table exists?

Basically, how do I do this "The Rails Way(tm)"

SELECT * FROM beta_users u LEFT JOIN beta_codes c ON = c.beta_user_id AND c.code IS NULL

At the moment I'm using find_by_sql, and that works Ok, but what would be the Rails way?

users = BetaCode.find(:all, :conditions => “code is null”).collect(&:beta_user)

This is assuming you have the following table definitions: class BetaUser < ActiveRecord::Base has_(one | many) :beta_code(s)

end class BetaCode < ActiveRecord::Base belongs_to :beta_user end

What this does is find all beta_codes whose code is null, and then iterates over them and finds their user… it would be similar to writing

null_codes = BetaCode.find(:all, :conditions => “code is null”) users = null_codes.each do |code| users << code.beta_user end

That wont work if there are no rows in the beta_codes table, which is the situation I'm in. I show a list of users who havn't been sent an invite code, and they only get an entry in that table when the invite has been sent.


@users = BetaUser.find :all, :include => "beta_codes", :conditions => "beta_codes.code IS NULL"

assuming that you have the association set up correctly:

BetaUser has_many:beta_code (or has_one) BetaCode belongs_to :beta_user