Bob Smith
2010-Aug-23 19:58 UTC
Trying to get a list of households with one person in them
@test = Household.count :all, :group => ''people.household_id'', :joins => :people -- 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.
Bob Smith
2010-Aug-23 20:03 UTC
Trying to get a list of households with one person in them
in my models, household has_many :people and people has_one :household household has a name field, and people has household_id the closest I got was: @test = Household.count :all, :group => ''people.household_id'', :joins => :people this does group the families together and count the members, but this has no way of including only families of one I am assuming that the output from this could be used to get the name field from households -- 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.
Colin Law
2010-Aug-23 20:15 UTC
Re: Trying to get a list of households with one person in them
On 23 August 2010 21:03, Bob Smith <bsm2th-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org> wrote:> in my models, household has_many :people and people has_one :household > household has a name field, and people has household_idThat should be person belongs_to household.> > the closest I got was: > @test = Household.count :all, :group => ''people.household_id'', :joins > => :people > > this does group the families together and count the members, > but this has no way of including only families of one > I am assuming that the output from this could be used to get the name > field from householdsIt might be worth looking at counter_cache. Colin -- 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.
Rob Biedenharn
2010-Aug-23 21:05 UTC
Re: Trying to get a list of households with one person in them
On Aug 23, 2010, at 4:15 PM, Colin Law wrote:> On 23 August 2010 21:03, Bob Smith <bsm2th-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org> wrote: >> in my models, household has_many :people and people >> has_one :household >> household has a name field, and people has household_id > > That should be person belongs_to household. > >> >> the closest I got was: >> @test = Household.count :all, :group => ''people.household_id'', :joins >> => :people >> >> this does group the families together and count the members, >> but this has no way of including only families of one >> I am assuming that the output from this could be used to get the name >> field from households > > It might be worth looking at counter_cache. > > ColinThat might not be a bad idea (or even running a Household.connection.select_value on the SQL). Well, the answer is going to be different for ActiveRecord 2.x and 3.0 Here''s how it would look in SQL SELECT COUNT(households.id) FROM households INNER JOIN people ON people.household_id = households.id GROUP BY households.id HAVING COUNT(people.id) = 1 In AR 2.x, that''s probably: Household .count (:include = > :people , :group=>''households.id'', :having=>''COUNT(people.id)=1'').first.first or since the join is simple and not truly needed: Person.count(:select => ''people.id'', :group => ''people.household_id'', :having => ''COUNT(id)=1'').first.first (the return will be an array of pairs [count,1] so [[count, 1]].first.first will be count) And in AR 3.0, something like: Household .select (''COUNT (households .id )'').includes (:people).group(''households.id'').having(''COUNT(people.id)=1'').to_a.size -or- Person .select (''COUNT(id)'').group(''household_id'').having(''COUNT(id)=1'').to_a.size You might be thinking "Hmm, ActiveRecord doesn''t seem to be well suited for a query like this" And I think you''d be right! -Rob Rob Biedenharn Rob-xa9cJyRlE0mWcWVYNo9pwxS2lgjeYSpx@public.gmane.org http://AgileConsultingLLC.com/ rab-/VpnD74mH8+00s0LW7PaslaTQe2KTcn/@public.gmane.org http://GaslightSoftware.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.
Sandy
2010-Aug-23 22:17 UTC
Re: Trying to get a list of households with one person in them
On Aug 23, 5:05 pm, Rob Biedenharn <R...-xa9cJyRlE0mWcWVYNo9pwxS2lgjeYSpx@public.gmane.org> wrote:> On Aug 23, 2010, at 4:15 PM, Colin Law wrote: > > > > > > > On 23 August 2010 21:03, Bob Smith <bsm...-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org> wrote: > >> in my models, household has_many :people and people > >> has_one :household > >> household has a name field, and people has household_id > > > That should be person belongs_to household. > > >> the closest I got was: > >> @test = Household.count :all, :group => ''people.household_id'', :joins > >> => :people > > >> this does group the families together and count the members, > >> but this has no way of including only families of one > >> I am assuming that the output from this could be used to get the name > >> field from households > > > It might be worth looking at counter_cache. > > > Colin > > That might not be a bad idea (or even running a > Household.connection.select_value on the SQL). > > Well, the answer is going to be different for ActiveRecord 2.x and 3.0 > > Here''s how it would look in SQL > > SELECT COUNT(households.id) FROM households > INNER JOIN people ON people.household_id = households.id > GROUP BY households.id > HAVING COUNT(people.id) = 1 > > In AR 2.x, that''s probably: > > Household > .count > (:include > > > > :people > , :group=>''households.id'', :having=>''COUNT(people.id)=1'').first.first > > or since the join is simple and not truly needed: > Person.count(:select => ''people.id'', :group => > ''people.household_id'', :having => ''COUNT(id)=1'').first.first > > (the return will be an array of pairs [count,1] so [[count, > 1]].first.first will be count) > > And in AR 3.0, something like: > > Household > .select > (''COUNT > (households > .id > )'').includes > (:people).group(''households.id'').having(''COUNT(people.id)=1'').to_a.size > -or- > Person > .select > (''COUNT(id)'').group(''household_id'').having(''COUNT(id)=1'').to_a.size > > You might be thinking "Hmm, ActiveRecord doesn''t seem to be well > suited for a query like this" > > And I think you''d be right! > > -Rob > > Rob Biedenharn > R...-xa9cJyRlE0mWcWVYNo9pwxS2lgjeYSpx@public.gmane.org http://AgileConsultingLLC.com/ > r...-/VpnD74mH8+00s0LW7PaslaTQe2KTcn/@public.gmane.org http://GaslightSoftware.com/- Hide quoted text - > > - Show quoted text -As Colin (sort of) suggested, your Household model should contain has_many :people and your Person model should contain belongs_to :household Don''t forget the colons, as shown above. Next, since the foregoing relationship will allow you to determine the number of People in each Household, I would stay away from trying to create an SQL statement, and, instead, allow the controller to provide the appropriate array to your view, as follows: def single_person_household households = Household.all @households_with_one = Array.new for h in households do if h.people.count == 1.to_i @households_with_one << h end end end NOTE: If you create a view called single_person_household.html.erb, then you will need a route that maps to that view in your routes.rb file, such as map.connect ''single_person_household'', :controller => ''households'', :action => ''single_person_household'' In your view, you will use @households_with_one. By the way, before I post code, I test it, so I know that I am actually answering the question which I understood to have been asked. Sandy -- 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@googlegroups.com. For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en.
Rob Biedenharn
2010-Aug-23 22:48 UTC
Re: Re: Trying to get a list of households with one person in them
On Aug 23, 2010, at 6:17 PM, Sandy wrote:> On Aug 23, 5:05 pm, Rob Biedenharn <R...-xa9cJyRlE0mWcWVYNo9pwxS2lgjeYSpx@public.gmane.org> > wrote: >> On Aug 23, 2010, at 4:15 PM, Colin Law wrote: >>> On 23 August 2010 21:03, Bob Smith <bsm...-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org> wrote: >>>> in my models, household has_many :people and people >>>> has_one :household >>>> household has a name field, and people has household_id >> >>> That should be person belongs_to household. >> >>>> the closest I got was: >>>> @test = Household.count :all, :group => >>>> ''people.household_id'', :joins >>>> => :people >> >>>> this does group the families together and count the members, >>>> but this has no way of including only families of one >>>> I am assuming that the output from this could be used to get the >>>> name >>>> field from households >> >>> It might be worth looking at counter_cache. >> >>> Colin >> >> That might not be a bad idea (or even running a >> Household.connection.select_value on the SQL). >> >> Well, the answer is going to be different for ActiveRecord 2.x and >> 3.0 >> >> Here''s how it would look in SQL >> >> SELECT COUNT(households.id) FROM households >> INNER JOIN people ON people.household_id = households.id >> GROUP BY households.id >> HAVING COUNT(people.id) = 1 >> >> In AR 2.x, that''s probably: >> >> Household >> .count >> (:include >> >> > >> :people >> , :group=>''households.id'', :having=>''COUNT(people.id)=1'').first.first >> >> or since the join is simple and not truly needed: >> Person.count(:select => ''people.id'', :group => >> ''people.household_id'', :having => ''COUNT(id)=1'').first.first >> >> (the return will be an array of pairs [count,1] so [[count, >> 1]].first.first will be count) >> >> And in AR 3.0, something like: >> >> Household >> .select >> (''COUNT >> (households >> .id >> )'').includes >> (:people >> ).group(''households.id'').having(''COUNT(people.id)=1'').to_a.size >> -or- >> Person >> .select >> (''COUNT(id)'').group(''household_id'').having(''COUNT(id)=1'').to_a.size >> >> You might be thinking "Hmm, ActiveRecord doesn''t seem to be well >> suited for a query like this" >> >> And I think you''d be right! >> >> -Rob >> >> Rob Biedenharn >> R...-xa9cJyRlE0mWcWVYNo9pwxS2lgjeYSpx@public.gmane.org http://AgileConsultingLLC.com/ >> r...-/VpnD74mH8+00s0LW7PaslaTQe2KTcn/@public.gmane.org http://GaslightSoftware.com/- >> Hide quoted text - >> >> - Show quoted text - > > As Colin (sort of) suggested, your Household model should contain > > has_many :people > > and your Person model should contain > > belongs_to :household > > Don''t forget the colons, as shown above. > > Next, since the foregoing relationship will allow you to determine the > number of People in each Household, I would stay away from trying to > create an SQL statement, and, instead, allow the controller to provide > the appropriate array to your view, as follows: > > def single_person_household > households = Household.all > @households_with_one = Array.new > for h in households do > if h.people.count == 1.to_i > @households_with_one << h > end > end > endBut since Bob hasn''t indicated the size of the Household and Person data sets, I''d caution that performance of that code on a large number of households could be dismal as it has to first read every household into an object (i.e., memory) and then make a separate query to the database to count the persons. Also, there''s never a need to do 1.to_i (1 is already an integer). If you really wanted to add this as Ruby code (rather than trying to leverage ActiveRecord directly, I''d suggest something at least a bit more idiomatic if no more efficient. class Person < ActiveRecord::Base belongs_to :household end class Household < ActiveRecord::Base has_many :people # reads all records, then a COUNT(*) query to determine people def self.single_person_households find(:all).select {|household| household.people.count == 1 } end # Or since the original question was for a count, # not the actual records... # Get just the COUNT of single person households def self.count_single_person_households connection.select_value(<<-ENDSQL) SELECT COUNT(households.id) AS the_count FROM households INNER JOIN people ON people.household_id = household.id GROUP BY households.id HAVING COUNT(people.id) = 1 ENDSQL end end puts Household.count_single_person_households Household.single_person_households.each do |household| puts household.name end -Rob> > NOTE: If you create a view called single_person_household.html.erb, > then you will need a route that maps to that view in your routes.rb > file, such as > > map.connect ''single_person_household'', :controller => > ''households'', :action => ''single_person_household'' > > In your view, you will use @households_with_one. > > By the way, before I post code, I test it, so I know that I am > actually answering the question which I understood to have been asked. > > Sandy >Rob Biedenharn Rob-xa9cJyRlE0mWcWVYNo9pwxS2lgjeYSpx@public.gmane.org http://AgileConsultingLLC.com/ rab-/VpnD74mH8+00s0LW7PaslaTQe2KTcn/@public.gmane.org http://GaslightSoftware.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.
Sandy
2010-Aug-23 23:18 UTC
Re: Trying to get a list of households with one person in them
On Aug 23, 6:48 pm, Rob Biedenharn <R...-xa9cJyRlE0mWcWVYNo9pwxS2lgjeYSpx@public.gmane.org> wrote:> On Aug 23, 2010, at 6:17 PM, Sandy wrote: > > > > > > > On Aug 23, 5:05 pm, Rob Biedenharn <R...-xa9cJyRlE0mWcWVYNo9pwxS2lgjeYSpx@public.gmane.org> > > wrote: > >> On Aug 23, 2010, at 4:15 PM, Colin Law wrote: > >>> On 23 August 2010 21:03, Bob Smith <bsm...-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org> wrote: > >>>> in my models, household has_many :people and people > >>>> has_one :household > >>>> household has a name field, and people has household_id > > >>> That should be person belongs_to household. > > >>>> the closest I got was: > >>>> @test = Household.count :all, :group => > >>>> ''people.household_id'', :joins > >>>> => :people > > >>>> this does group the families together and count the members, > >>>> but this has no way of including only families of one > >>>> I am assuming that the output from this could be used to get the > >>>> name > >>>> field from households > > >>> It might be worth looking at counter_cache. > > >>> Colin > > >> That might not be a bad idea (or even running a > >> Household.connection.select_value on the SQL). > > >> Well, the answer is going to be different for ActiveRecord 2.x and > >> 3.0 > > >> Here''s how it would look in SQL > > >> SELECT COUNT(households.id) FROM households > >> INNER JOIN people ON people.household_id = households.id > >> GROUP BY households.id > >> HAVING COUNT(people.id) = 1 > > >> In AR 2.x, that''s probably: > > >> Household > >> .count > >> (:include > >> > > >> :people > >> , :group=>''households.id'', :having=>''COUNT(people.id)=1'').first.first > > >> or since the join is simple and not truly needed: > >> Person.count(:select => ''people.id'', :group => > >> ''people.household_id'', :having => ''COUNT(id)=1'').first.first > > >> (the return will be an array of pairs [count,1] so [[count, > >> 1]].first.first will be count) > > >> And in AR 3.0, something like: > > >> Household > >> .select > >> (''COUNT > >> (households > >> .id > >> )'').includes > >> (:people > >> ).group(''households.id'').having(''COUNT(people.id)=1'').to_a.size > >> -or- > >> Person > >> .select > >> (''COUNT(id)'').group(''household_id'').having(''COUNT(id)=1'').to_a.size > > >> You might be thinking "Hmm, ActiveRecord doesn''t seem to be well > >> suited for a query like this" > > >> And I think you''d be right! > > >> -Rob > > >> Rob Biedenharn > >> R...-xa9cJyRlE0mWcWVYNo9pwxS2lgjeYSpx@public.gmane.org http://AgileConsultingLLC.com/ > >> r...-/VpnD74mH8+00s0LW7PaslaTQe2KTcn/@public.gmane.org http://GaslightSoftware.com/- > >> Hide quoted text - > > >> - Show quoted text - > > > As Colin (sort of) suggested, your Household model should contain > > > has_many :people > > > and your Person model should contain > > > belongs_to :household > > > Don''t forget the colons, as shown above. > > > Next, since the foregoing relationship will allow you to determine the > > number of People in each Household, I would stay away from trying to > > create an SQL statement, and, instead, allow the controller to provide > > the appropriate array to your view, as follows: > > > def single_person_household > > households = Household.all > > @households_with_one = Array.new > > for h in households do > > if h.people.count == 1.to_i > > @households_with_one << h > > end > > end > > end > > But since Bob hasn''t indicated the size of the Household and Person > data sets, I''d caution that performance of that code on a large number > of households could be dismal as it has to first read every household > into an object (i.e., memory) and then make a separate query to the > database to count the persons. > > Also, there''s never a need to do 1.to_i (1 is already an integer). > > If you really wanted to add this as Ruby code (rather than trying to > leverage ActiveRecord directly, I''d suggest something at least a bit > more idiomatic if no more efficient. > > class Person < ActiveRecord::Base > belongs_to :household > end > > class Household < ActiveRecord::Base > has_many :people > > # reads all records, then a COUNT(*) query to determine people > def self.single_person_households > find(:all).select {|household| household.people.count == 1 } > end > > # Or since the original question was for a count, > # not the actual records... > # Get just the COUNT of single person households > def self.count_single_person_households > connection.select_value(<<-ENDSQL) > SELECT COUNT(households.id) AS the_count > FROM households > INNER JOIN people ON people.household_id = household.id > GROUP BY households.id > HAVING COUNT(people.id) = 1 > ENDSQL > end > end > > puts Household.count_single_person_households > Household.single_person_households.each do |household| > puts household.name > end > > -Rob > > > > > NOTE: If you create a view called single_person_household.html.erb, > > then you will need a route that maps to that view in your routes.rb > > file, such as > > > map.connect ''single_person_household'', :controller => > > ''households'', :action => ''single_person_household'' > > > In your view, you will use @households_with_one. > > > By the way, before I post code, I test it, so I know that I am > > actually answering the question which I understood to have been asked. > > > Sandy > > Rob Biedenharn > R...-xa9cJyRlE0mWcWVYNo9pwxS2lgjeYSpx@public.gmane.org http://AgileConsultingLLC.com/ > r...-/VpnD74mH8+00s0LW7PaslaTQe2KTcn/@public.gmane.org http://GaslightSoftware.com/- Hide quoted text - > > - Show quoted text -Rob, I agree that SQL is more efficient than the Ruby code. However, not every application requires the ultimate in database ''efficiency'', and my experience is that such efficiency, at the expense of coding time and clarity, is often overrated As far as the "original question" goes, he said, "I am assuming that the output from this could be used to get the name field from households." The fact that he wants the name field from the households implies that he needs the array such that he can then display the name field. The count, alone, doesn''t supply the name field. Sandy -- 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@googlegroups.com. For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en.
Bob Smith
2010-Aug-26 05:37 UTC
Re: Trying to get a list of households with one person in them
On Aug 23, 7:18 pm, Sandy <sandy.as...-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org> wrote:> On Aug 23, 6:48 pm, Rob Biedenharn <R...-xa9cJyRlE0mWcWVYNo9pwxS2lgjeYSpx@public.gmane.org> > wrote: > > > > > On Aug 23, 2010, at 6:17 PM, Sandy wrote: > > > > On Aug 23, 5:05 pm, Rob Biedenharn <R...-xa9cJyRlE0mWcWVYNo9pwxS2lgjeYSpx@public.gmane.org> > > > wrote: > > >> On Aug 23, 2010, at 4:15 PM, Colin Law wrote: > > >>> On 23 August 2010 21:03, Bob Smith <bsm...-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org> wrote: > > >>>> in my models, household has_many :people and people > > >>>> has_one :household > > >>>> household has a name field, and people has household_id > > > >>> That should bepersonbelongs_to household. > > > >>>> the closest I got was: > > >>>> @test = Household.count:all, :group => > > >>>> ''people.household_id'', :joins > > >>>> => :people > > > >>>> this does group the families together andcountthe members, > > >>>> but this has no way of including only families of one > > >>>> I am assuming that the output from this could be used to get the > > >>>> name > > >>>> field from households > > > >>> It might be worth looking at counter_cache. > > > >>> Colin > > > >> That might not be a bad idea (or even running a > > >> Household.connection.select_value on the SQL). > > > >> Well, the answer is going to be different for ActiveRecord 2.x and > > >> 3.0 > > > >> Here''s how it would look in SQL > > > >> SELECTCOUNT(households.id) FROM households > > >> INNER JOIN people ON people.household_id = households.id > > >> GROUP BY households.id > > >> HAVINGCOUNT(people.id) = 1 > > > >> In AR 2.x, that''s probably: > > > >> Household > > >> .count > > >> (:include > > >> > > > >> :people > > >> , :group=>''households.id'', :having=>''COUNT(people.id)=1'').first.first > > > >> or since the join is simple and not truly needed: > > >>Person.count(:select => ''people.id'', :group => > > >> ''people.household_id'', :having => ''COUNT(id)=1'').first.first > > > >> (the return will be an array of pairs [count,1] so [[count, > > >> 1]].first.first will becount) > > > >> And in AR 3.0, something like: > > > >> Household > > >> .select > > >> (''COUNT > > >> (households > > >> .id > > >> )'').includes > > >> (:people > > >> ).group(''households.id'').having(''COUNT(people.id)=1'').to_a.size > > >> -or- > > >>Person > > >> .select > > >> (''COUNT(id)'').group(''household_id'').having(''COUNT(id)=1'').to_a.size > > > >> You might be thinking "Hmm, ActiveRecord doesn''t seem to be well > > >> suited for a query like this" > > > >> And I think you''d be right! > > > >> -Rob > > > >> Rob Biedenharn > > >> R...-xa9cJyRlE0mWcWVYNo9pwxS2lgjeYSpx@public.gmane.org http://AgileConsultingLLC.com/ > > >> r...-/VpnD74mH8+00s0LW7PaslaTQe2KTcn/@public.gmane.org http://GaslightSoftware.com/- > > >> Hide quoted text - > > > >> - Show quoted text - > > > > As Colin (sort of) suggested, your Household model should contain > > > > has_many :people > > > > and yourPersonmodel should contain > > > > belongs_to :household > > > > Don''t forget the colons, as shown above. > > > > Next, since the foregoing relationship will allow you to determine the > > > number of People in each Household, I would stay away from trying to > > > create an SQL statement, and, instead, allow the controller to provide > > > the appropriate array to your view, as follows: > > > > def single_person_household > > > households = Household.all > > > @households_with_one = Array.new > > > for h in households do > > > if h.people.count== 1.to_i > > > @households_with_one << h > > > end > > > end > > > end > > > But since Bob hasn''t indicated the size of the Household andPerson > > data sets, I''d caution that performance of that code on a large number > > of households could be dismal as it has to first read every household > > into an object (i.e., memory) and then make a separate query to the > > database tocountthe persons. > > > Also, there''s never a need to do 1.to_i (1 is already an integer). > > > If you really wanted to add this as Ruby code (rather than trying to > > leverage ActiveRecord directly, I''d suggest something at least a bit > > more idiomatic if no more efficient. > > > classPerson< ActiveRecord::Base > > belongs_to :household > > end > > > class Household < ActiveRecord::Base > > has_many :people > > > # reads all records, then aCOUNT(*) query to determine people > > def self.single_person_households > > find(:all).select {|household| household.people.count== 1 } > > end > > > # Or since the original question was for acount, > > # not the actual records... > > # Get just theCOUNTof singlepersonhouseholds > > def self.count_single_person_households > > connection.select_value(<<-ENDSQL) > > SELECTCOUNT(households.id) AS the_count > > FROM households > > INNER JOIN people ON people.household_id = household.id > > GROUP BY households.id > > HAVINGCOUNT(people.id) = 1 > > ENDSQL > > end > > end > > > puts Household.count_single_person_households > > Household.single_person_households.each do |household| > > puts household.name > > end > > > -Rob > > > > NOTE: If you create a view called single_person_household.html.erb, > > > then you will need a route that maps to that view in your routes.rb > > > file, such as > > > > map.connect ''single_person_household'', :controller => > > > ''households'', :action => ''single_person_household'' > > > > In your view, you will use @households_with_one. > > > > By the way, before I post code, I test it, so I know that I am > > > actually answering the question which I understood to have been asked. > > > > Sandy > > > Rob Biedenharn > > R...-xa9cJyRlE0mWcWVYNo9pwxS2lgjeYSpx@public.gmane.org http://AgileConsultingLLC.com/ > > r...-/VpnD74mH8+00s0LW7PaslaTQe2KTcn/@public.gmane.org http://GaslightSoftware.com/-Hide quoted text - > > > - Show quoted text - > > Rob, > > I agree that SQL is more efficient than the Ruby code. However, not > every application requires the ultimate in database ''efficiency'', and > my experience is that such efficiency, at the expense of coding time > and clarity, is often overrated > > As far as the "original question" goes, he said, "I am assuming that > the output from this could be used to get the name field from > households." The fact that he wants the name field from the > households implies that he needs the array such that he can then > display the name field. Thecount, alone, doesn''t supply the name > field. > > SandyWorked perfectly again Sandy. Thanks Bob -- 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@googlegroups.com. For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en.