Using models as join tables query question

rubyonrails-talk
1

I can't seem to think straight this evening. I have the following models

class Candidate < ActiveRecord::Base   has_many :castings   has_many :projects, :through => :castings end

class Project < ActiveRecord::Base   has_many :castings   has_many :candidates, :through => :castings end

class Casting < ActiveRecord::Base   belongs_to :project   belongs_to :candidate end

I need to get a list of projects that the candidate has NO castings for. The opposite of candidate.projects. Any zen way to do it? Make it fit into a form.select ?

2

has_many :projects_uncasted_for, :class_name => 'Project', :finder_sql => <<-'end'   select *   from projects left join castings on projects.id = castings.project_id   where castings.candidate_id is null or castings.candidate_id != #{id} end

3

Thanks Eden. I changed the query to

select DISTINCT projects.* from projects left join castings on projects.id = castings.project_id where castings.candidate_id is null or castings.candidate_id != #{id}

and now it seems works great.