CODEWITHSUNDEEP
sqlruby-on-railsruby-on-rails-3findassociations
Andrew
Andrew

Reputation: 43113

Rails: Find the "has_one" record that doesn't have one

So, I have an app with two models. Foo has_one Bar, Bar belongs_to Foo.

Now, to create a Foo you must to have to create a Bar to belong to it, but it looks like something slipped through the cracks because in my production app I now seem to have one Foo that somehow got created without a Bar, and it causes a 500 error.

Now, here's the problem:

I can search: Bar.where(:foo=>nil) just fine. But orphan bars aren't a problem, and this doesn't tell me what I need.

I need to find the one Foo where Bar is nil. But the database stores the relationship in the Bars table, ie, BarsTable has foo_id in it, there's nothing in the FoosTable to tell me it's missing a bar.

When I use Foo.find(#).bar I would get nil for the one bogus record, but I have a lot of records.

So, can anyone tell me how to construct a query that would return the one Foo that is missing it's Bar?

Thanks!!

Upvotes: 10

Views: 3145

Answers (4)

Mark Swardstrom
Mark Swardstrom

Reputation: 18070

Use left_outer_join for this.

Foo.left_outer_joins(:bar).where(bars: { id: nil })

Rails added a nice convenience method for this situation

Foo.where.missing(:bar)

Upvotes: 1

Babbz77
Babbz77

Reputation: 321

For future user visitors another way to accomplish this is by doing -

Foo.all.where.not(id: Bar.pluck(:belongs_to_id))

This does two queries one for the pluck and one for the Foo.where.not(id: plucked_ids).

Upvotes: 3

Kevin Galens
Kevin Galens

Reputation: 31

Another way (without using SQL) would be to do something like:

Foo.all.select{ |f| !f.bar }

This would return an array of Foo objects which don't have a related Bar object.

In this method, you're not relying on specific table information. If the foreign_key column were to change in the future Foo -> Bar association, this method would continue to work.

Upvotes: 2

salexander
salexander

Reputation: 954

I'm not sure what the Ruby code would be, but I think the SQL should be something like:

SELECT * FROM Foo WHERE id NOT IN (SELECT foo_id FROM Bar)

Upvotes: 8

Related Questions