Rails, ActiveRecord, how to find foreign rows that are NOT there?

Question

The title is a bit difficult to put together for this question.

Say I have two models, Foo and Bar. Foo has many Bar's. Bar belongs to one Foo. How do I use Foo.find to find all of the Foos that currently have zero Bars? In SQL this would translate into something like:

SELECT * from foo where id NOT IN (select foo_id from bar);

Answer 1

Foo.all(:conditions => ["id NOT IN (?)", Bar.all.map(&:foo_id)]) will do it, though there are likely optimizations you can take from there.

If there are speed concerns with this, you might want to look into using a counter cache, which would turn the query into Foo.find_all_by_bars_count(0)

Documentation on counter caches is here.