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.
D0cd6b10e01bacb976b3b815a9c660bc?d=identicon&s=25 Alex Wayne (squeegy)
on 2007-05-24 04: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!
83c8cca11558f32207645ca270ae8475?d=identicon&s=25 sishen (Guest)
on 2007-05-24 05: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....
Bc7e893b6d4605000478c871b7ce8862?d=identicon&s=25 Gabe Da silveira (dasil003)
on 2007-05-24 06: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 Wayne 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!
588ab1c0a5610a7e160a3b101abb91e6?d=identicon&s=25 MichaelLatta (Guest)
on 2007-05-24 08: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 Wayne <rails-mailing-l...@andreas-s.net>
D0cd6b10e01bacb976b3b815a9c660bc?d=identicon&s=25 Alex Wayne (squeegy)
on 2007-05-24 20: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 Wayne 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.