Forum: Ruby on Rails How do I find records with no associated objects?

Announcement (2017-05-07): www.ruby-forum.com is now read-only since I unfortunately do not have the time to support and maintain the forum any more. Please see rubyonrails.org/community and ruby-lang.org/en/community for other Rails- und Ruby-related community platforms.
Alex W. (Guest)
on 2007-05-24 06:41
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!
sishen (Guest)
on 2007-05-24 07:35
(Received via mailing list)
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....
Gabe D. (Guest)
on 2007-05-24 08:51
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!
MichaelLatta (Guest)
on 2007-05-24 10:42
(Received via mailing list)
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>
Alex W. (Guest)
on 2007-05-24 22:26
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.
This topic is locked and can not be replied to.