rails scope to check if association does NOT exist

sql ruby-on-rails associations scopes
Julio G Medina picture Julio G Medina · Apr 27, 2012 · Viewed 18.2k times · Source

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

davegson picture davegson · Apr 30, 2014

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

Related questions

Rails raw SQL example

How can I convert this code to raw sql and use in rails? Because When I deploy this code in heroku,there is a request timeout error.I think this will be faster if I use raw sql. @payments = PaymentDetail.…

Read More
Rails 4 LIKE query - ActiveRecord adds quotes

I am trying to do a like query like so def self.search(search, page = 1 ) paginate :per_page => 5, :page => page, :conditions => ["name LIKE '%?%' OR postal_code like '%?%'", search, search], order => 'name' end …

Read More
Rails 3 execute custom sql query without a model

I need to write a standalone ruby script that is supposed to deal with database. I used code given below in rails 3 @connection = ActiveRecord::Base.establish_connection( :adapter => "mysql2", :host => "localhost", :database => "siteconfig_development", :username => "root", :…

Read More