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 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
> [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 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 [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.
