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