# Query across tables

**URL:** <https://www.ruby-forum.com/t/query-across-tables/101441>\
**Category:** Rails\
**Created:** [August 4, 2006, 8:53pm UTC](https://www.ruby-forum.com/t/query-across-tables/101441 "2006-08-04T20:53:36Z")\
**Posts on this page:** 6\
**Page:** 1

<div class="post-metadata">

**Author:** ![bob](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/b/ea666f/32.png) [@bob](https://www.ruby-forum.com/u/bob)\
**Post date:** [August 4, 2006, 8:53pm UTC](https://www.ruby-forum.com/t/query-across-tables/101441/1 "2006-08-04T20:53:36Z")

</div>

I am trying to do things the proper rails way and use the auto generated  
id to refernce other tables. Now I’ve hit a wall in trying to write a  
query.

timesheets:  
id, employee\_id, code\_id, … values

codes:  
id, job\_number, code

How can I show just the timesheets that belong to a specific job?  
I would need to check the codes table and look at the jobs, so I was  
thinking of something like  
Timesheet.find(:all, :order =\> “employee\_id, code\_id”, :conditions  
=\>[??] )

but the documentation says that :conditions is supposed to be a sql like  
statement. Perhaps I need to do a join in order to get this to work?

Bob

---

<div class="post-metadata">

**Author:** ![bob](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/b/ea666f/32.png) [@bob](https://www.ruby-forum.com/u/bob)\
**Post date:** [August 4, 2006, 9:16pm UTC](https://www.ruby-forum.com/t/query-across-tables/101441/2 "2006-08-04T21:16:00Z")

</div>

@code = Code.find\_by\_job\_number, :include =\>[:timesheets]

\<% code.timesheets.each do |timesheet| %\>  
\<%=timesheet.foo %\>  
\<% end %\>

You can also nest the includes so you can include the employee  
information  
into the select as well… if that’s what you need.

@code = Code.find\_by\_job\_number, :include =\>[{:timesheets =\> :employee}]

How’s that?

---

<div class="post-metadata">

**Author:** ![bob](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/b/ea666f/32.png) [@bob](https://www.ruby-forum.com/u/bob)\
**Post date:** [August 4, 2006, 9:16pm UTC](https://www.ruby-forum.com/t/query-across-tables/101441/3 "2006-08-04T21:16:00Z")

</div>

Whoops… should be

@code = Code.find\_by\_job\_number 1234, :include =\>[:timesheets]

or

@code = Code.find\_by\_job\_number 1234, :include =\>[{:timesheets =\>  
:employee}]

---

<div class="post-metadata">

**Author:** ![bob](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/b/ea666f/32.png) [@bob](https://www.ruby-forum.com/u/bob)\
**Post date:** [August 4, 2006, 9:22pm UTC](https://www.ruby-forum.com/t/query-across-tables/101441/4 "2006-08-04T21:22:04Z")

</div>

I think I understand what you are asking for but could be wrong.

If the job\_number is some arbitratry number without a Model  
associated to it. Than this query should work: (note the space at the  
beginning of the :joins (for some reason you have to add it as  
the :joins option does not.) At least it doesn’t in 1.1.2 and I have  
not fully looked at 1.1.4 to see if it was fixed.

Timesheet.find(:all,  
:select =\> “timesheets.\*”,  
:joins =\> " , codes",  
:conditions =\> [“timesheets.code\_id = codes.id AND  
codes.job\_number = ?”, somevariable])

The :select option is only going to bring back fields from the  
timesheets table. If you want all fields from both codes and  
timesheets you can remove it.

Others use INNER and outer joins and left joins and all that other  
fun stuff however I find it just as easy and sometimes faster to do  
it this way.

I found that the :joins param really just throws anything you want  
before the WHERE clause of a query so you can add any SQL in there  
that you like.

Andrew

---

<div class="post-metadata">

**Author:** ![bob](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/b/ea666f/32.png) [@bob](https://www.ruby-forum.com/u/bob)\
**Post date:** [August 15, 2006, 2:13pm UTC](https://www.ruby-forum.com/t/query-across-tables/101441/5 "2006-08-15T14:13:17Z")

</div>

@code = Code.find\_by\_job\_number 1234, :include =\>[{:timesheets =\>  
:employee}]

That returns the code (singular)

So to grab the timesheets, just do

@code = Code.find\_by\_job\_number 1234, :include =\>[{:timesheets =\>  
:employee}]  
@timesheets = @code.timesheets

---

<div class="post-metadata">

**Author:** ![bob](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/b/ea666f/32.png) [@bob](https://www.ruby-forum.com/u/bob)\
**Post date:** [August 4, 2006, 11:02pm UTC](https://www.ruby-forum.com/t/query-across-tables/101441/6 "2006-08-04T23:02:36Z")

</div>

Andrew - it worked great, thanks for the help and the explanation! By  
the way, I tried it without the space before the join and it still  
worked.

Brian, is that statement returning the codes? I was trying to go the  
other way around, and grab the timesheets unless I’m not understanding  
the code. Either way, I appreciate the help.

Bob