I asked this in a different form some weeks ago and got no response, let me try again in hopes that I can make my question clearer. Let''s say I have the following models class Author < ActiveRecord::Base has_many :signings end class Signing < ActiveRecord::Base belongs_to: :author # Has an attribute when, which is a DateTime end Now what I want to do is find all authors who have at least one signing scheduled on or after a particular date. In other words, I want to exclude authors who won''t be signing on or after that date. Since I don''t have a black belt in SQL, I''m not sure how to do this. I think I want some kind of join of authors to signings, and a query which groups by author and a where clause involving the maximum value of where for each signing in the group. But I''m having a hard time visualizing the actual SQL for this. And for extra points, is there a way to do this with find, :include =>, :conditions, rather than find_by_sql? -- Rick DeNatale My blog on Ruby http://talklikeaduck.denhaven2.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-/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 10/16/07, Rick DeNatale <rick.denatale-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org> wrote:> > I asked this in a different form some weeks ago and got no response, > let me try again in hopes that I can make my question clearer. > > Let''s say I have the following models > > class Author < ActiveRecord::Base > has_many :signings > end > > class Signing < ActiveRecord::Base > belongs_to: :author > # Has an attribute when, which is a DateTime > end > > Now what I want to do is find all authors who have at least one > signing scheduled on or after a particular date. In other words, I > want to exclude authors who won''t be signing on or after that date.results = Author.find :all, :include => :signings, :conditions => [''signings.when >= ?'', ''2007-10-01''] --~--~---------~--~----~------------~-------~--~----~ 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 -~----------~----~----~----~------~----~------~--~---
Try something along the lines of: Author.find(:all, :include => [:signings], :conditions => ["signing.when > ?", Date.today]) I''m not 100% sure of the syntax of the condition string, but I''m pretty sure thats right. On Oct 16, 8:24 am, "Rick DeNatale" <rick.denat...-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org> wrote:> I asked this in a different form some weeks ago and got no response, > let me try again in hopes that I can make my question clearer. > > Let''s say I have the following models > > class Author < ActiveRecord::Base > has_many :signings > end > > class Signing < ActiveRecord::Base > belongs_to: :author > # Has an attribute when, which is a DateTime > end > > Now what I want to do is find all authors who have at least one > signing scheduled on or after a particular date. In other words, I > want to exclude authors who won''t be signing on or after that date. > > Since I don''t have a black belt in SQL, I''m not sure how to do this. > > I think I want some kind of join of authors to signings, and a query > which groups by author and a where clause involving the maximum value > of where for each signing in the group. But I''m having a hard time > visualizing the actual SQL for this. > > And for extra points, is there a way to do this with find, :include > =>, :conditions, rather than find_by_sql? > > -- > Rick DeNatale > > My blog on Rubyhttp://talklikeaduck.denhaven2.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-/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 10/16/07, Bob Showalter <showaltb-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org> wrote:> > On 10/16/07, Rick DeNatale <rick.denatale-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org> wrote: > > > > I asked this in a different form some weeks ago and got no response, > > let me try again in hopes that I can make my question clearer. > > > > Let''s say I have the following models > > > > class Author < ActiveRecord::Base > > has_many :signings > > end > > > > class Signing < ActiveRecord::Base > > belongs_to: :author > > # Has an attribute when, which is a DateTime > > end > > > > Now what I want to do is find all authors who have at least one > > signing scheduled on or after a particular date. In other words, I > > want to exclude authors who won''t be signing on or after that date. > > results = Author.find :all, > :include => :signings, > :conditions => [''signings.when >= ?'', ''2007-10-01'']Thanks that SEEMS to work although I''m not sure exactly why. This generates SQL: SELECT authors.`id` AS t0_r0, authors.`name` AS t0_r1, signings.`when` AS t1_r2, signings.`location` AS t1_r3 FROM authors LEFT OUTER JOIN signings ON signings.author_id = author.id WHERE (signings.begin_date >= ''2007-10-16 10:45:46'') As I understand it the left outer join produces a reiation which would have multiple rows for the same author. So why don''t I get multiple copies of authors. (Admittedly tis is an intentionally naive question, but I''m curious). -- Rick DeNatale My blog on Ruby http://talklikeaduck.denhaven2.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-/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 -~----------~----~----~----~------~----~------~--~---
You will get multiples. From what I see, you should get one per signing that occurs after that date. It''s possible the rails code is internally making them unique? --Michael --~--~---------~--~----~------------~-------~--~----~ 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 16 Oct 2007, at 16:20, Rick DeNatale wrote:> > Thanks that SEEMS to work although I''m not sure exactly why. This > generates SQL: > > SELECT authors.`id` AS t0_r0, authors.`name` AS t0_r1, > signings.`when` AS t1_r2, signings.`location` AS t1_r3 FROM authors > LEFT OUTER JOIN signings ON signings.author_id = author.id WHERE > (signings.begin_date >= ''2007-10-16 10:45:46'') > > As I understand it the left outer join produces a reiation which would > have multiple rows for the same author. So why don''t I get multiple > copies of authors. (Admittedly tis is an intentionally naive question, > but I''m curious).short answer: becase the rails code sorts that out for you (or :include would be pretty useless). Long answer: read associations.rb Fred> -- > Rick DeNatale > > My blog on Ruby > http://talklikeaduck.denhaven2.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-/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 10/16/07, Michael Graff <skan.gryphon-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org> wrote:> > You will get multiples. From what I see, you should get one per > signing that occurs after that date. It''s possible the rails code is > internally making them unique?Yes, I looked at the ActiveRecord code and this is what it seems to be doing. When you do a find involving associations (e.g. the find is scoped or has the :include option), then AR instantiates an internal class called JoinDependency, it then does the query and calls the instantiate method on the JoinDependency. This builds a hash table keyed by the primary key of the base table of the join, and uses this to instantiate one and only one AR object for each id. It then pushes the appropriate other objects to the base objects association collections from each row of the result. -- Rick DeNatale My blog on Ruby http://talklikeaduck.denhaven2.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-/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 10/16/07, Rick DeNatale <rick.denatale-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org> wrote:> > On 10/16/07, Michael Graff <skan.gryphon-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org> wrote: > > > > You will get multiples. From what I see, you should get one per > > signing that occurs after that date. It''s possible the rails code is > > internally making them unique? > > Yes, I looked at the ActiveRecord code and this is what it seems to be doing. > > When you do a find involving associations (e.g. the find is scoped or > has the :include option), then AR instantiates an internal class > called JoinDependency, it then does the query and calls the > instantiate method on the JoinDependency. > > This builds a hash table keyed by the primary key of the base table of > the join, and uses this to instantiate one and only one AR object for > each id. It then pushes the appropriate other objects to the base > objects association collections from each row of the result."Pay no attention to that man behind the curtain!" :~) --~--~---------~--~----~------------~-------~--~----~ 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 -~----------~----~----~----~------~----~------~--~---
I''ve been looking at this a bit more, and it raises a concern. While results = Author.find :all, :include => :signings, :conditions => [''signings.when >= ?'', ''2007-10-01''] works, it instantiates an AR object for each signing, effectively pre-loading the signings association for each author. Many times this is what''s wanted However, since my example is really an analog for a more complex situtation, what if there are LOTS of signings, and they are fairly heavy-weight objects. The use case here is that I want to present a list of ''Authors'' filtered by those who actually have a ''signing'' to attend, and then when an author is selected to present his/her signings for selection. Is there an easy way to do the search to find all of the ''authors'' but leave instantiating the signings association collection until it''s needed? I tried (Item is the real name for the Author analog, and Schedules for signing: Item.find(:all, :joins => '' LEFT OUTER JOIN schedules ON schedules.item_id'', :conditions => [''schedules.begin_date > ?'', Time.now]) But that returns 26520 Item objects while: Item.find(:all).length => 34 The problem here is that one AR object is being instantiated for each row returned by the outer join. I imagine that it''s an enhancement request to be able to have my cake and eat it too, i.e. to be able to ''include'' :schedules for purpose of the where clause, and to maintain identity for the resulting Item objects, but NOT pre-instantiate the schedules association. Now I could of course get the expanded list and uniq it, but if I benchmark:>> Item.benchmark(''join'') {Item.find(:all, :joins => '' LEFT OUTER JOINschedules ON schedules.item_id'', :conditions => [''schedules.begin_date> ?'', Time.now]).length}=> 26520>> Item.benchmark(''include'') {Item.find(:all, :include => :schedules,:conditions => [''schedules.begin_date > ?'', Time.now]).length}=> 25 The log shows: join (2.27320) include (0.18201) So the cat''s already out of the bag as far as time efficiency. -- Rick DeNatale My blog on Ruby http://talklikeaduck.denhaven2.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-/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 17 Oct 2007, at 16:37, Rick DeNatale wrote:> > I''ve been looking at this a bit more, and it raises a concern. > > While > > results = Author.find :all, > :include => :signings, > :conditions => [''signings.when >= ?'', ''2007-10-01''] >> > I tried (Item is the real name for the Author analog, and Schedules > for signing: > Item.find(:all, :joins => '' LEFT OUTER JOIN schedules ON > schedules.item_id'', :conditions => [''schedules.begin_date > ?'', > Time.now]) > >Try Item.find :all, :select => ''items.*'', :group => ''items.id'', :joins => ..., :conditions => ... Fred --~--~---------~--~----~------------~-------~--~----~ 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 -~----------~----~----~----~------~----~------~--~---
find_by_sql? This should let you hand-craft exactly the data you want. --Michael --~--~---------~--~----~------------~-------~--~----~ 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 -~----------~----~----~----~------~----~------~--~---
Looks like you want a GROUP BY * and drop the signings from the output (unless you want to grab a count...) On Oct 17, 10:56 am, "Michael Graff" <skan.gryp...-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org> wrote:> find_by_sql? This should let you hand-craft exactly the data you want. > > --Michael--~--~---------~--~----~------------~-------~--~----~ 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 -~----------~----~----~----~------~----~------~--~---