Julio G Medina Julio G Medina - 5 months ago 51
SQL Question

rails scope to check if association does NOT exist

I am looking toward writing a scope that returns all records that do not have a particular association.

foo.rb

class Foo < ActiveRecord::Base
has_many :bars
end


bar.rb

class Bar < ActiveRecord::Base
belongs_to :foo
end


I want a scope that can find all of the
Foo's
that dont have any
bars
. It's easy to find the ones that have an association using
joins
, but I haven't found a way to do the opposite.

Answer

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) }