Here are three examples which I would expect to be the same. The
first works best. The second is not functional, since the Address
records come back with nil ids. The third works ok, but duplicates
the condition in the WHERE clause. (Bonus points if you can get
that condition to go on the ON clause of the INNER JOIN).
This is ActiveRecord 2.2
class Address < ActiveRecord::Base
# doesn't quite work
named_scope :not_invited, :joins => "LEFT JOIN invites ON addresses.id = invites.address_id",
:conditions => "invites.id IS NULL"
end
class User < ActiveRecord::Base
has_and_belongs_to_many :contacts, :class_name => 'Address'
def contacts_not_invited
contacts.all(:joins => "LEFT JOIN invites ON addresses.id = invites.address_id",
:conditions => "invites.id IS NULL")
end
end
current_user = User.find_by_email("aaron@example.com")
p current_user.contacts_not_invited
# => Address Load (0.0ms) SELECT "addresses".* FROM "addresses" INNER JOIN "addresses_users" ON "addresses".id = "addresses_users".address_id LEFT JOIN invites ON addresses.id = invites.address_id WHERE (invites.id IS NULL) AND ("addresses_users".user_id = 317248456 )
# => [#<Address id: 718047205, email: "woo@thanks.com">, #<Address id: 1068445024, email: "newguy@place.com">]
p current_user.contacts.not_invited
# => Address Load (0.0ms) SELECT * FROM "addresses" LEFT JOIN invites ON addresses.id = invites.address_id INNER JOIN "addresses_users" ON "addresses".id = "addresses_users".address_id WHERE ("addresses_users".user_id = 317248456 ) AND ((invites.id IS NULL) AND ("addresses_users".user_id = 317248456 ))
# XXX: address records ids are nil!!
# => [#<Address id: nil, email: "woo@thanks.com">, #<Address id: nil, email: "newguy@place.com">]
p current_user.contacts.not_invited.all
# => Address Load (0.0ms) SELECT "addresses".* FROM "addresses" LEFT JOIN invites ON addresses.id = invites.address_id INNER JOIN "addresses_users" ON "addresses".id = "addresses_users".address_id WHERE (((invites.id IS NULL) AND ("addresses_users".user_id = 317248456 )) AND ("addresses_users".user_id = 317248456 ))
# => [#<Address id: 718047205, email: "woo@thanks.com">, #<Address id: 1068445024, email: "newguy@place.com">]