rails scope to check if association does NOT exist
I prefer to use squeel gem to build complex queries. It extends ActiveRecord with such a magic:
Foo.where{id.not_in Bar.select{foo_id}.uniq}
that builds the following query:
SELECT "foos".*
FROM "foos"
WHERE "foos"."id" NOT IN (
SELECT DISTINCT "bars"."foo_id"
FROM "bars"
)
So,
# in Foo class
scope :lonely, where{id.not_in Bar.select{foo_id}.uniq}
is what you can use to build the requested scope.
Rails 4 makes this too easy :)
Foo.where.not(id: Bar.select(:foo_id).uniq)
this outputs the same query as jdoe's answer
SELECT "foos".*
FROM "foos"
WHERE "foos"."id" NOT IN (
SELECT DISTINCT "bars"."foo_id"
FROM "bars"
)
And as a scope:
scope :lonely, -> { where.not(id: Bar.select(:item_id).uniq) }
in foo.rb
class Foo < ActiveRecord::Base
has_many :bars
scope :lonely, lambda { joins('LEFT OUTER JOIN bars ON foos.id = bars.foo_id').where('bars.foo_id IS NULL') }
end
For Rails 5+ (Ruby 2.4.1 & Postgres 9.6)
I have 100 foos
and 9900 bars
. 99 of the foos
each have 100 bars
, and one of them has none.
Foo.left_outer_joins(:bars).where(bars: { foo_id: nil })
Produces one SQL query:
Foo Load (2.3ms) SELECT "foos".* FROM "foos" LEFT OUTER JOIN "bars" ON "bars"."foo_id" = "foos"."id" WHERE "bars"."foo_id" IS NULL
and returns the one Foo
with no bars
The currently accepted answer Foo.where.not(id: Bar.select(:foo_id).uniq)
is not working. It is producing two SQL queries:
Bar Load (8.4ms) SELECT "bars"."foo_id" FROM "bars"
Foo Load (0.3ms) SELECT "foos".* FROM "foos" WHERE ("foos"."id" IS NOT NULL)
which returns all foos
because all foos
have an id
that is not null.
It needs to be changed to Foo.where.not(id: Bar.pluck(:foo_id).uniq)
to reduce it to one query and find our Foo
, but it performs poorly in benchmarks
require 'benchmark/ips'
require_relative 'config/environment'
Benchmark.ips do |bm|
bm.report('left_outer_joins') do
Foo.left_outer_joins(:bars).where(bars: { foo_id: nil })
end
bm.report('where.not') do
Foo.where.not(id: Bar.pluck(:foo_id).uniq)
end
bm.compare!
end
Warming up --------------------------------------
left_outer_joins 1.143k i/100ms
where.not 6.000 i/100ms
Calculating -------------------------------------
left_outer_joins 13.659k (± 9.0%) i/s - 68.580k in 5.071807s
where.not 70.856 (± 9.9%) i/s - 354.000 in 5.057443s
Comparison:
left_outer_joins: 13659.3 i/s
where.not: 70.9 i/s - 192.77x slower