CODEWITHSUNDEEP
ruby-on-railsactiverecord
jspooner
jspooner

Reputation: 11315

ActiveRecord: Find without associations

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

Upvotes: 0

Views: 1475

Answers (3)

Peer Allan
Peer Allan

Reputation: 4004

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.

Upvotes: 8

Shadwell
Shadwell

Reputation: 34774

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.

Upvotes: 0

jspooner
jspooner

Reputation: 11315

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

Upvotes: 1

Related Questions