sqlruby-on-railsassociationsscopes

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.


Solution

  • Rails 4 makes this too easy :)

    Foo.where.not(id: Bar.select(:foo_id).distinct)
    

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