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

**URL:** <https://www.ruby-forum.com/t/trying-to-get-a-list-of-households-with-one-person-in-them/193748>\
**Category:** Rails\
**Created:** [August 23, 2010, 10:04pm UTC](https://www.ruby-forum.com/t/trying-to-get-a-list-of-households-with-one-person-in-them/193748 "2010-08-23T22:04:31Z")\
**Posts on this page:** 6\
**Page:** 1

<div class="post-metadata">

**Author:** ![adistarmid](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/a/f14d63/32.png) [@adistarmid](https://www.ruby-forum.com/u/adistarmid)\
**Post date:** [August 23, 2010, 10:04pm UTC](https://www.ruby-forum.com/t/trying-to-get-a-list-of-households-with-one-person-in-them/193748/1 "2010-08-23T22:04:31Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![adistarmid](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/a/f14d63/32.png) [@adistarmid](https://www.ruby-forum.com/u/adistarmid)\
**Post date:** [August 23, 2010, 10:17pm UTC](https://www.ruby-forum.com/t/trying-to-get-a-list-of-households-with-one-person-in-them/193748/2 "2010-08-23T22:17:25Z")

</div>

On 23 August 2010 21:03, Bob S. [removed\_email\_address@domain.invalid](mailto:removed_email_address@domain.invalid) 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

---

<div class="post-metadata">

**Author:** ![adistarmid](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/a/f14d63/32.png) [@adistarmid](https://www.ruby-forum.com/u/adistarmid)\
**Post date:** [August 23, 2010, 11:06pm UTC](https://www.ruby-forum.com/t/trying-to-get-a-list-of-households-with-one-person-in-them/193748/3 "2010-08-23T23:06:01Z")

</div>

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.  
removed\_email\_address@domain.invalid [http://AgileConsultingLLC.com/](http://AgileConsultingLLC.com/)  
removed\_email\_address@domain.invalid [http://GaslightSoftware.com/](http://GaslightSoftware.com/)

---

<div class="post-metadata">

**Author:** ![adistarmid](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/a/f14d63/32.png) [@adistarmid](https://www.ruby-forum.com/u/adistarmid)\
**Post date:** [August 24, 2010, 12:49am UTC](https://www.ruby-forum.com/t/trying-to-get-a-list-of-households-with-one-person-in-them/193748/4 "2010-08-24T00:49:38Z")

</div>

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.  
removed\_email\_address@domain.invalid [http://AgileConsultingLLC.com/](http://AgileConsultingLLC.com/)  
removed\_email\_address@domain.invalid [http://GaslightSoftware.com/](http://GaslightSoftware.com/)

---

<div class="post-metadata">

**Author:** ![adistarmid](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/a/f14d63/32.png) [@adistarmid](https://www.ruby-forum.com/u/adistarmid)\
**Post date:** [August 24, 2010, 12:19am UTC](https://www.ruby-forum.com/t/trying-to-get-a-list-of-households-with-one-person-in-them/193748/5 "2010-08-24T00:19:12Z")

</div>

On Aug 23, 5:05 pm, Rob B. [removed\_email\_address@domain.invalid](mailto:removed_email_address@domain.invalid)  
wrote:

> =
> 
> Person  
> Rob B.  
> removed\_email\_address@domain.invalid [http://AgileConsultingLLC.com/](http://AgileConsultingLLC.com/)  
> removed\_email\_address@domain.invalid [http://GaslightSoftware.com/-](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

---

<div class="post-metadata">

**Author:** ![adistarmid](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/a/f14d63/32.png) [@adistarmid](https://www.ruby-forum.com/u/adistarmid)\
**Post date:** [August 24, 2010, 1:20am UTC](https://www.ruby-forum.com/t/trying-to-get-a-list-of-households-with-one-person-in-them/193748/6 "2010-08-24T01:20:42Z")

</div>

On Aug 23, 6:48 pm, Rob B. [removed\_email\_address@domain.invalid](mailto:removed_email_address@domain.invalid)  
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.  
> removed\_email\_address@domain.invalid [http://AgileConsultingLLC.com/](http://AgileConsultingLLC.com/)  
> removed\_email\_address@domain.invalid [http://GaslightSoftware.com/-](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