# Wants to get all parents with no children, etc

**URL:** <https://www.ruby-forum.com/t/wants-to-get-all-parents-with-no-children-etc/124765>\
**Category:** Rails\
**Created:** [January 2, 2008, 11:49am UTC](https://www.ruby-forum.com/t/wants-to-get-all-parents-with-no-children-etc/124765 "2008-01-02T11:49:33Z")\
**Posts on this page:** 2\
**Page:** 1

<div class="post-metadata">

**Author:** ![Kei\_Simone](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/k/839c29/32.png) [@Kei\_Simone](https://www.ruby-forum.com/u/Kei_Simone)\
**Post date:** [January 2, 2008, 11:49am UTC](https://www.ruby-forum.com/t/wants-to-get-all-parents-with-no-children-etc/124765/1 "2008-01-02T11:49:33Z")

</div>

To all:

hi,

i have implemented the associations in my models

so i have a Parent model that has many Children model

but i need to get a list of Parents that do not have children  
or  
Parents that have just 2 children

or Parents that have children whose name is ‘James’, or ‘Sarah’, or  
‘Betty’

or Parents whose children do not have names like ‘James’, or ‘Sarah’,  
or ‘Betty’

I find it near impossible to use activerecord and the various find  
methods.

Maybe I overlook something.

Please advise

Thank you.

---

<div class="post-metadata">

**Author:** ![Kei\_Simone](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/k/839c29/32.png) [@Kei\_Simone](https://www.ruby-forum.com/u/Kei_Simone)\
**Post date:** [January 2, 2008, 12:38pm UTC](https://www.ruby-forum.com/t/wants-to-get-all-parents-with-no-children-etc/124765/2 "2008-01-02T12:38:07Z")

</div>

On 2 Jan 2008, at 10:48, Kei Simone wrote:

> or  
> Parents that have just 2 children

> or Parents that have children whose name is ‘James’, or ‘Sarah’, or  
> ‘Betty’
> 
> or Parents whose children do not have names like ‘James’, or ‘Sarah’,  
> or ‘Betty’
> 
> I find it near impossible to use activerecord and the various find  
> methods.

You’re right that activerecord won’t help you much with this sort of  
stuff - you will be writing out a bunch of joins yourself (with the  
exception of ‘parents with 2 children’: you can use a counter\_cache,  
at which point it becomes trivial).  
To get all parents with no children, you need to be generating sql of  
the form

select parents.\* from parents  
left outer join children on children.parent\_id = parents.id  
where children.id IS NULL

if you want parents whose children match some condition

select parents.\* from parents  
inner join children on children.parent\_id = parents.id  
where

if you want parents with no children matching some condition

select parents.\* from parents  
left outer join children on children.parent\_id = parents.id AND   
where children.id IS NULL

You can tell activerecord what joins to apply with the :joins option

Fred