# Using models as join tables query question

**URL:** <https://www.ruby-forum.com/t/using-models-as-join-tables-query-question/103428>\
**Category:** Rails\
**Created:** [May 5, 2007, 9:24pm UTC](https://www.ruby-forum.com/t/using-models-as-join-tables-query-question/103428 "2007-05-05T21:24:24Z")\
**Posts on this page:** 3\
**Page:** 1

<div class="post-metadata">

**Author:** ![Tomislav\_FilipSSSSi](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/t/ee59a6/32.png) [@Tomislav\_FilipSSSSi](https://www.ruby-forum.com/u/Tomislav_FilipSSSSi)\
**Post date:** [May 5, 2007, 9:24pm UTC](https://www.ruby-forum.com/t/using-models-as-join-tables-query-question/103428/1 "2007-05-05T21:24:24Z")

</div>

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 ?

---

<div class="post-metadata">

**Author:** ![Tomislav\_FilipSSSSi](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/t/ee59a6/32.png) [@Tomislav\_FilipSSSSi](https://www.ruby-forum.com/u/Tomislav_FilipSSSSi)\
**Post date:** [May 6, 2007, 6:08am UTC](https://www.ruby-forum.com/t/using-models-as-join-tables-query-question/103428/2 "2007-05-06T06:08:58Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![Tomislav\_FilipSSSSi](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/t/ee59a6/32.png) [@Tomislav\_FilipSSSSi](https://www.ruby-forum.com/u/Tomislav_FilipSSSSi)\
**Post date:** [May 6, 2007, 3:10pm UTC](https://www.ruby-forum.com/t/using-models-as-join-tables-query-question/103428/3 "2007-05-06T15:10:37Z")

</div>

On May 6, 6:07 am, eden li [removed\_email\_address@domain.invalid](mailto:removed_email_address@domain.invalid) wrote:

> has\_many :projects\_uncasted\_for, :class\_name =\> ‘Project’, :finder\_sql  
> =\> \<\<-‘end’  
> select \*  
> from projects left joincastingson projects.id =castings.project\_id  
> wherecastings.candidate\_id is null orcastings.candidate\_id !=  
> #{id}  
> end

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.