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 -- Posted via http://www.ruby-forum.com/.
@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?
On 8/4/06, Bob <test@example.com> wrote:>
> 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
>
> --
> Posted via http://www.ruby-forum.com/.
> _______________________________________________
> Rails mailing list
> Rails@lists.rubyonrails.org
> http://lists.rubyonrails.org/mailman/listinfo/rails
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL:
http://wrath.rubyonrails.org/pipermail/rails/attachments/20060804/26a4c3aa/attachment-0001.html
Whoops... should be
@code = Code.find_by_job_number 1234, :include =>[:timesheets]
or
@code = Code.find_by_job_number 1234, :include =>[{:timesheets =>
:employee}]
On 8/4/06, Brian Hogan <bphogan@gmail.com> wrote:>
> @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?
>
>
> On 8/4/06, Bob < test@example.com> wrote:
> >
> > 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
> >
> > --
> > Posted via http://www.ruby-forum.com/.
> > _______________________________________________
> > Rails mailing list
> > Rails@lists.rubyonrails.org
> > http://lists.rubyonrails.org/mailman/listinfo/rails
> >
>
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL:
http://wrath.rubyonrails.org/pipermail/rails/attachments/20060804/877b5035/attachment.html
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 On Aug 4, 2006, at 2:53 PM, Bob wrote:> 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 > > -- > Posted via http://www.ruby-forum.com/. > _______________________________________________ > Rails mailing list > Rails@lists.rubyonrails.org > http://lists.rubyonrails.org/mailman/listinfo/rails
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 -- Posted via http://www.ruby-forum.com/.
@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
On 8/4/06, Bob <test@example.com> wrote:>
> 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
>
> --
> Posted via http://www.ruby-forum.com/.
> _______________________________________________
> Rails mailing list
> Rails@lists.rubyonrails.org
> http://lists.rubyonrails.org/mailman/listinfo/rails
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL:
http://wrath.rubyonrails.org/pipermail/rails/attachments/20060810/30034bb2/attachment.html