Rails - Apr 2007 - 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 u.id 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?


--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en
-~----------~----~----~----~------~----~------~--~---

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

On 4/6/07, Stuart Grimshaw
<stuart.grimshaw-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org>
wrote:
>
>
> Basically, how do I do this "The Rails Way(tm)"
>
> SELECT * FROM beta_users u LEFT JOIN beta_codes c ON u.id >
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?
>
>
> >
>
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en
-~----------~----~----~----~------~----~------~--~---

On Apr 6, 9:37 pm, "Luke Ivers"
<technod...-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org>
wrote:
> 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.


--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en
-~----------~----~----~----~------~----~------~--~---

maybe:

@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


On 7 Apr., 17:47, "Stuart  Grimshaw"
<stuart.grims...-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org>
wrote:
> On Apr 6, 9:37 pm, "Luke Ivers"
<technod...-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org> wrote:
>
>
>
> > 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.

--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en
-~----------~----~----~----~------~----~------~--~---