Query across tables

Rails
1

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

2

@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?

3

Whoops… should be

@code = Code.find_by_job_number 1234, :include =>[:timesheets]

or

@code = Code.find_by_job_number 1234, :include =>[{:timesheets =>
:employee}]

4

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

5

@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

6

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