ActiveRecord: Find without associations

Question

I have a many_to_many relationship between ImageShells and Users. I need to find all ImageShells that have no users.

I have a query that finds them but how do I put this into a named_scope?

SELECT * FROM image_shells INNER JOIN image_shells_users ON (image_shells_users.image_shell_id!=image_shells.id)


class ImageShell < ActiveRecord::Base
   has_and_belongs_to_many :riders, :class_name => "User"
end

class User < ActiveRecord::Base  
  has_and_belongs_to_many :image_shells
end

I'm able to use find by sql but this is messy.

img_shells_with_out_users = ImageShell.find_by_sql 'SELECT * FROM image_shells INNER JOIN image_shells_users ON (image_shells_users.image_shell_id!=image_shells.id)'

Answer 1

Super easy

  named_scope :with_out_users, {
:select => "image_shells.*",
:joins => "INNER JOIN image_shells_users ON (image_shells_users.image_shell_id!=image_shells.id)" 
  }

Answer 2

named_scope :without_users, 
  :conditions => "not exists (select * from image_shells_users where
                  image_shell_users.image_shell_id = image_shells.id)"

The SQL you provided in the question doesn't actually find image shells without users it actually joins the image shell to every user in the database that has an image shell except the image shell you are querying from.

Answer 3

Should do the trick

named_scope :without_users, { :include => :users, 
   :conditions => 'image_shell_users.user_id IS NULL' }

ActiveRecord takes care of generating all the correct joins for you when using the include option. In order to do the include the users it has to go through the join table and so you can use it in your conditions.