Given
A has_many :B :through => :C
A has_many :C
B has_many :A :through => :C
B has_many :C
C belongs_to :A
C belongs_to :B
What is the idiomatic way to find all B that are not associated with a
given value for A? This will work but is there a simpler way?
this_a = A.find_by_attribute(''value'')
all_b = B.find(:all)
assigned = this_a.bs
unassigned = all_b - assigned
--
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-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk+unsubscribe-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en.
Let me rephrase the question. How would one construct an AR find so as to return the desired subset of B records in a single SQL query? -- 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-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org To unsubscribe from this group, send email to rubyonrails-talk+unsubscribe-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en.
Marnen Laibow-Koser
2010-Jan-18 18:42 UTC
Re: AR - find all records not already associated
James Byrne wrote:> Given > > A has_many :B :through => :C > A has_many :C > > B has_many :A :through => :C > B has_many :C > > C belongs_to :A > C belongs_to :B > > What is the idiomatic way to find all B that are not associated with a > given value for A? This will work but is there a simpler way? > > this_a = A.find_by_attribute(''value'') > all_b = B.find(:all) > assigned = this_a.bs > unassigned = all_b - assignedHere''s sample SQL -- you can work out the find! SELECT * from b LEFT JOIN c on (c.b_id = b.id) LEFT JOIN a on (c.a_id = a.id) WHERE a.id != 17 # or whatever value Best, -- Marnen Laibow-Koser http://www.marnen.org marnen-sbuyVjPbboAdnm+yROfE0A@public.gmane.org -- Posted via http://www.ruby-forum.com/. --00163645889a790845047d74b766 Content-Type: text/plain; charset=ISO-8859-1 -- 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-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org To unsubscribe from this group, send email to rubyonrails-talk+unsubscribe-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en. --00163645889a790845047d74b766--
On Jan 18, 1:42 pm, Marnen Laibow-Koser <li...-fsXkhYbjdPsEEoCn2XhGlw@public.gmane.org> wrote:> > Here''s sample SQL -- you can work out the find! >Thanks. I will have a go at it. -- 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-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org To unsubscribe from this group, send email to rubyonrails-talk+unsubscribe-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en.
On Jan 18, 6:42 pm, Marnen Laibow-Koser <li...-fsXkhYbjdPsEEoCn2XhGlw@public.gmane.org> wrote:> James Byrne wrote: > > Given > > > A has_many :B :through => :C > > A has_many :C > > > B has_many :A :through => :C > > B has_many :C > > > C belongs_to :A > > C belongs_to :B > > > What is the idiomatic way to find all B that are not associated with a > > given value for A? This will work but is there a simpler way? > > > this_a = A.find_by_attribute(''value'') > > all_b = B.find(:all) > > assigned = this_a.bs > > unassigned = all_b - assigned > > Here''s sample SQL -- you can work out the find! > > SELECT * from b LEFT JOIN c on (c.b_id = b.id) LEFT JOIN a on (c.a_id > a.id) > WHERE a.id != 17 # or whatever valueI don''t think that''s quite right - if the c table contains a_id b_id 1 2 17 2 3 2 Then clearly the b with id 2 is associated with the a with id 17, but I believe the above query would return the b with id 2 (twice) something like select * from b left join c on c.b_id = b.id and c.a_id = 17 left join a on c.a_id = a.id where a.id is null should do the trick (and if you have foreign key constraints then you don''t ever need to join the a table Fred> > Best, > -- > Marnen Laibow-Koserhttp://www.marnen.org > mar...-sbuyVjPbboAdnm+yROfE0A@public.gmane.org > -- > Posted viahttp://www.ruby-forum.com/.-- 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-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org To unsubscribe from this group, send email to rubyonrails-talk+unsubscribe-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en.