# Querying an unrelated table

**URL:** <https://www.ruby-forum.com/t/querying-an-unrelated-table/113436>\
**Category:** Rails\
**Created:** [August 31, 2007, 1:40am UTC](https://www.ruby-forum.com/t/querying-an-unrelated-table/113436 "2007-08-31T01:40:48Z")\
**Posts on this page:** 5\
**Page:** 1

<div class="post-metadata">

**Author:** ![dracos](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/d/ea666f/32.png) [@dracos](https://www.ruby-forum.com/u/dracos)\
**Post date:** [August 31, 2007, 1:40am UTC](https://www.ruby-forum.com/t/querying-an-unrelated-table/113436/1 "2007-08-31T01:40:48Z")

</div>

I have three tables: Case, Attorney and Firm. Case belongs to Attorney  
and Attorney Belongs to Firm. I don’t see how I can define that Case  
belongs to Firm through Attorney since Attorney is not a join table  
(does not reference both). In order to find all cases for a Firm, it  
seems I must write a sequence of queries like this:

class FirmsController \< ApplicationController

def show  
id = params[:id]  
@attorneys = Attorney.find(:all, :conditions =\> [“firm\_id = ?”, id])  
@cases = Case.find(:all, :conditions =\> [“attorney\_id = ?”,  
@attorneys.id])  
end

end

In this sequence however, @cases doesn’t return any case objects. How  
might I get this to work?

Thanks,

Peter

---

<div class="post-metadata">

**Author:** ![dracos](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/d/ea666f/32.png) [@dracos](https://www.ruby-forum.com/u/dracos)\
**Post date:** [August 31, 2007, 2:18am UTC](https://www.ruby-forum.com/t/querying-an-unrelated-table/113436/2 "2007-08-31T02:18:12Z")

</div>

Peter M. wrote:

> def show  
> id = params[:id]  
> @attorneys = Attorney.find(:all, :conditions =\> [“firm\_id = ?”, id])  
> @cases = Case.find(:all, :conditions =\> [“attorney\_id = ?”,  
> @attorneys.id])  
> end

@cases = Case.find(:all,  
:include =\> [{ :attorney =\> :firm }],  
:conditions =\> [“firm.id = ?”, params[:id]])

Clifford H…

---

<div class="post-metadata">

**Author:** ![dracos](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/d/ea666f/32.png) [@dracos](https://www.ruby-forum.com/u/dracos)\
**Post date:** [August 31, 2007, 2:22am UTC](https://www.ruby-forum.com/t/querying-an-unrelated-table/113436/3 "2007-08-31T02:22:54Z")

</div>

Clifford H. wrote:

> Peter M. wrote:
> 
> > def show  
> > id = params[:id]  
> > @attorneys = Attorney.find(:all, :conditions =\> [“firm\_id = ?”, id])  
> > @cases = Case.find(:all, :conditions =\> [“attorney\_id = ?”,  
> > @attorneys.id])  
> > end
> 
> @cases = Case.find(:all,  
> :include =\> [{ :attorney =\> :firm }],  
> :conditions =\> [“firm.id = ?”, params[:id]])
> 
> Clifford H…

Excellent. Thanks Clifford. 🙂

---

<div class="post-metadata">

**Author:** ![dracos](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/d/ea666f/32.png) [@dracos](https://www.ruby-forum.com/u/dracos)\
**Post date:** [August 31, 2007, 2:51am UTC](https://www.ruby-forum.com/t/querying-an-unrelated-table/113436/4 "2007-08-31T02:51:00Z")

</div>

Peter M. wrote:

> I have three tables: Case, Attorney and Firm. Case belongs to Attorney  
> and Attorney Belongs to Firm. I don’t see how I can define that Case  
> belongs to Firm through Attorney since Attorney is not a join table  
> (does not reference both). In order to find all cases for a Firm, it  
> seems I must write a sequence of queries like this:
> 
> class FirmsController \< ApplicationController
> 
> def show  
> id = params[:id]  
> @attorneys = Attorney.find(:all, :conditions =\> [“firm\_id = ?”, id])  
> @cases = Case.find(:all, :conditions =\> [“attorney\_id = ?”,  
> @attorneys.id])  
> end
> 
> end
> 
> In this sequence however, @cases doesn’t return any case objects. How  
> might I get this to work?

As others have pointed out, you can use :include to find things. You  
might also want to use an asymetric join model.

Attorney  
has\_many :cases

Firm  
has\_many :attorneys  
has\_many :cases, :through =\> :attorneys

@firm = Firm.find(params[:id])  
@cases = @firm.cases

That will work when the join model is like yours, a belongs\_to and a  
has\_many.

–  
Josh S.  
[http://blog.hasmanythrough.com](http://blog.hasmanythrough.com)

---

<div class="post-metadata">

**Author:** ![dracos](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/d/ea666f/32.png) [@dracos](https://www.ruby-forum.com/u/dracos)\
**Post date:** [August 31, 2007, 4:34am UTC](https://www.ruby-forum.com/t/querying-an-unrelated-table/113436/5 "2007-08-31T04:34:22Z")

</div>

Peter M. wrote:  
…

> > > ```
> > > @cases = Case.find(:all, :conditions => ["attorney_id = ?",
> > > 
> > > ```
> > > 
> > > @attorneys.id])  
> > > Excellent. Thanks Clifford. 🙂

BTW, @attorneys.id isn’t what you think, in your original code.  
You got the Ruby object\_id, not the id attribute from ActiveRecord.  
This is a major trap, because it sometimes does what you want.

Just ALWAYS write @thing[:id] instead… or build your schemas with  
config.active\_record.primary\_key\_prefix\_type = :table\_name  
and suffer the fact that the generators might not all do the right  
thing.

Clifford H.