named scoped NOT IN, how to?

Question

how can I write a NOT IN in named scope syntax? For example, User :has_many Photos, how can I define:

User.has_no_photo

and returns all users who are not in the Photo model? thanks!

Answer 1

ActiveRecord isn't different from what you would do in SQL:

class User < ActiveRecord::Base
  has_many :photos
  named_scope :has_no_photos,
    :conditions => "(SELECT COUNT(#{Photo.table_name}.*)
                     FROM #{Photo.table_name}
                     WHERE #{Photo.table_name}.user_id = #{User.table_name}.id) = 0"
end

There are probably more efficient ways of doing this. One would be to store the photos_count on the user rows: then the query becomes a simple photos_count = 0, instead of a complex join:

class Photo < ActiveRecord::Base
  belongs_to :user, :counter_cache => true
end

class User < ActiveRecord::Base
  has_many :photos
  named_scope :has_no_photos, :conditions => {:photos_count => 0}
end

Answer 2

named_scope :has_no_photos, :conditions => [ 
  "id NOT IN ?", 
  Photo.all(:select => "distinct user_id").map(&:user_id).map(&:to_i)
]

I assume you have a standard "Photo.belongs_to :user" association, and integer keys