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