# How do I find records with no associated objects?

**URL:** <https://www.ruby-forum.com/t/how-do-i-find-records-with-no-associated-objects/104939>\
**Category:** Rails\
**Created:** [May 24, 2007, 4:41am UTC](https://www.ruby-forum.com/t/how-do-i-find-records-with-no-associated-objects/104939 "2007-05-24T04:41:35Z")\
**Posts on this page:** 5\
**Page:** 1

<div class="post-metadata">

**Author:** ![squeegy](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/s/dec6dc/32.png) [@squeegy](https://www.ruby-forum.com/u/squeegy)\
**Post date:** [May 24, 2007, 4:41am UTC](https://www.ruby-forum.com/t/how-do-i-find-records-with-no-associated-objects/104939/1 "2007-05-24T04:41:35Z")

</div>

I have a “Product has\_many :sales” relationship in my app, and I need to  
find all Product objects that have no associated Sale objects. Normally  
I could just find on a null foreign key, but that wont work with a  
has\_many since the foreign key is stored in the associated object.

So what does my :conditions need to be find all items that have zero  
items in a has\_many association?

Thanks for the help!

---

<div class="post-metadata">

**Author:** ![squeegy](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/s/dec6dc/32.png) [@squeegy](https://www.ruby-forum.com/u/squeegy)\
**Post date:** [May 24, 2007, 5:35am UTC](https://www.ruby-forum.com/t/how-do-i-find-records-with-no-associated-objects/104939/2 "2007-05-24T05:35:51Z")

</div>

Can this work? Just pseud-code.

met = Product.find(:all).inject([]) { |array, product| array \<\< product  
unless product.sales.any? }

However, i think the performance is low…

---

<div class="post-metadata">

**Author:** ![squeegy](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/s/dec6dc/32.png) [@squeegy](https://www.ruby-forum.com/u/squeegy)\
**Post date:** [May 24, 2007, 6:51am UTC](https://www.ruby-forum.com/t/how-do-i-find-records-with-no-associated-objects/104939/3 "2007-05-24T06:51:53Z")

</div>

Product.find( :all,  
:select =\> “DISTINCT products.\*”,  
:joins =\> “LEFT JOIN sales ON products.id = sales.product\_id”,  
:conditions =\> “sales.id IS NULL”)

This would have to be a bit different if eager loading was involved.

Alex W. wrote:

> I have a “Product has\_many :sales” relationship in my app, and I need to  
> find all Product objects that have no associated Sale objects. Normally  
> I could just find on a null foreign key, but that wont work with a  
> has\_many since the foreign key is stored in the associated object.
> 
> So what does my :conditions need to be find all items that have zero  
> items in a has\_many association?
> 
> Thanks for the help!

---

<div class="post-metadata">

**Author:** ![squeegy](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/s/dec6dc/32.png) [@squeegy](https://www.ruby-forum.com/u/squeegy)\
**Post date:** [May 24, 2007, 8:42am UTC](https://www.ruby-forum.com/t/how-do-i-find-records-with-no-associated-objects/104939/4 "2007-05-24T08:42:03Z")

</div>

You will need to use a subquery in the condition that tests for not  
exists(foreign\_key\_in\_other\_table = this\_table.id)

Michael

On May 23, 7:41 pm, Alex W. [removed\_email\_address@domain.invalid](mailto:removed_email_address@domain.invalid)

---

<div class="post-metadata">

**Author:** ![squeegy](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/s/dec6dc/32.png) [@squeegy](https://www.ruby-forum.com/u/squeegy)\
**Post date:** [May 24, 2007, 8:26pm UTC](https://www.ruby-forum.com/t/how-do-i-find-records-with-no-associated-objects/104939/5 "2007-05-24T20:26:03Z")

</div>

Gabe Da silveira wrote:

> Product.find( :all,  
> :select =\> “DISTINCT products.\*”,  
> :joins =\> “LEFT JOIN sales ON products.id = sales.product\_id”,  
> :conditions =\> “sales.id IS NULL”)
> 
> This would have to be a bit different if eager loading was involved.
> 
> Alex W. wrote:
> 
> > I have a “Product has\_many :sales” relationship in my app, and I need to  
> > find all Product objects that have no associated Sale objects. Normally  
> > I could just find on a null foreign key, but that wont work with a  
> > has\_many since the foreign key is stored in the associated object.
> > 
> > So what does my :conditions need to be find all items that have zero  
> > items in a has\_many association?
> > 
> > Thanks for the help!

Thank you. That seems to work perfectly, although I have no idea why.  
TIme to brush up on some SQL I think.