On Aug 23, 7:18 pm, Sandy <[email protected]> wrote: > On Aug 23, 6:48 pm, Rob Biedenharn <[email protected]> > wrote: > > > > > On Aug 23, 2010, at 6:17 PM, Sandy wrote: > > > > On Aug 23, 5:05 pm, Rob Biedenharn <[email protected]> > > > wrote: > > >> On Aug 23, 2010, at 4:15 PM, Colin Law wrote: > > >>> On 23 August 2010 21:03, Bob Smith <[email protected]> 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 > > >> [email protected] http://AgileConsultingLLC.com/ > > >> [email protected] 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 > > [email protected] http://AgileConsultingLLC.com/ > > [email protected] 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. > > Sandy
Worked 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 [email protected]. To unsubscribe from this group, send email to [email protected]. For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en.
