Complicated named_scope: Find users who don't belong to a certain project

Question

I'm trying to create a named scope like User.not_in_project(project) but I can't find the right way.

I have Users, Projects and Duties as a join model:

class User < ActiveRecord::Base
  has_many :duties, :extend => FindByAssociatedExtension
  has_many :projects, :through => :duties
end

class Duty < ActiveRecord::Base
  belongs_to :user
  belongs_to :project
end

class Project < ActiveRecord::Base
  has_many :duties
  has_many :users, :through => :duties
end

I tried with a named_scope similar to this find clause:

User.all(:joins => :duties, :conditions => ['duties.project_id != ?', my_project])

But that doesn't return me users who don't have my_project but users that have a project other than my_project.

In other words, I want the named scope to behave exactly like this method:

def self.not_present_in p
  self.all.reject{|u| u.projects.include?(p)}
end

How can I do that?

Answer 1

Thinking in SQL, the query should be something like:

select id
  from users
 where id not in (select id
                    from users join duties on users.id = duties.user_id
                    join projects on duties.project_id = projects.id
                   where projects.id = %)

But I'm not too sure how it would work using named_scope. I'd say use something like

def self.not_present_in p
  find_by_sql ["select id from users where id not in (select id from users join duties on users.id = duties.user_id join projects on duties.project_id = projects.id where projects.id = ?)", p]
end

Not as pretty as using AR, but will work (and save you some queries, probably).