How do I find records with no associated objects?

Rails
1

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!

2

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…

3

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!

4

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. [email protected]

5

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.