Trying to get a list of households with one person in them

Rails
1

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

2

On 23 August 2010 21:03, Bob S. [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

3

On Aug 23, 2010, at 4:15 PM, Colin L. wrote:

=> :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 B.
[email protected] http://AgileConsultingLLC.com/
[email protected] http://GaslightSoftware.com/

4

On Aug 23, 2010, at 6:17 PM, Sandy wrote:

the closest I got was:
It might be worth looking at counter_cache.

=

-or-

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

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 B.
[email protected] http://AgileConsultingLLC.com/
[email protected] http://GaslightSoftware.com/

5

On Aug 23, 5:05 pm, Rob B. [email protected]
wrote:

=

Person
Rob B.
[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

6

On Aug 23, 6:48 pm, Rob B. [email protected]
wrote:

in my models, household has_many :people and people
this does group the families together and count the members,
Household.connection.select_value on the SQL).

or since the join is simple and not truly needed:
('COUNT
You might be thinking "Hmm, ActiveRecord doesn’t seem to be well

Don’t forget the colons, as shown above.
if h.people.count == 1.to_i

class Household < ActiveRecord::Base
def self.count_single_person_households
puts Household.count_single_person_households

file, such as

Rob B.
[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. The count, alone, doesn’t supply the name
field.

Sandy