I am trying to run a pair of sql statements with the second using a value form the first''s returned data set but can figure out the syntax. @risks returns the values I would expect but @changes gets no returned data obvioulsy because it doesn''t see the contenst of @risks.id. def reportbyowner2 @risks = Risk.find_by_sql(["SELECT * FROM risks r where r.status ''Open'' and r.owner_id = ? order by r.id", @params[''owner'']]) @changes = Change.find_by_sql(["SELECT * FROM changes c where c.risk_id = ? order by c.id", @risks.id]) end Any help please. Many thanks. -- Posted via http://www.ruby-forum.com/. --~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the Google Groups "Ruby on Rails: Talk" group. To post to this group, send email to rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org To unsubscribe from this group, send email to rubyonrails-talk+unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en -~----------~----~----~----~------~----~------~--~---
On Fri, Sep 5, 2008 at 8:47 PM, Martyn Elmy-liddiard < rails-mailing-list-ARtvInVfO7ksV2N9l4h3zg@public.gmane.org> wrote:> > I am trying to run a pair of sql statements with the second using a > value form the first''s returned data set but can figure out the syntax. > > @risks returns the values I would expect but @changes gets no returned > data obvioulsy because it doesn''t see the contenst of @risks.id. > > def reportbyowner2 > @risks = Risk.find_by_sql(["SELECT * FROM risks r where r.status > ''Open'' and r.owner_id = ? order by r.id", @params[''owner'']]) > @changes = Change.find_by_sql(["SELECT * FROM changes c where c.risk_id > = ? order by c.id", @risks.id])How many records are there in the @risks object. You''re doing c.risk_id=@ risks.id, so if there are multiple rows in @risks then the query wont return any result.> end > > Any help please. > > Many thanks. > -- > Posted via http://www.ruby-forum.com/. > > > >~~ Shiv N Gautam --~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the Google Groups "Ruby on Rails: Talk" group. To post to this group, send email to rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org To unsubscribe from this group, send email to rubyonrails-talk+unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en -~----------~----~----~----~------~----~------~--~---
Shiv N Gautam wrote:> On Fri, Sep 5, 2008 at 8:47 PM, Martyn Elmy-liddiard < > rails-mailing-list-ARtvInVfO7ksV2N9l4h3zg@public.gmane.org> wrote: > >> @changes = Change.find_by_sql(["SELECT * FROM changes c where c.risk_id >> = ? order by c.id", @risks.id]) > > > How many records are there in the @risks object. You''re doing > c.risk_id=@ > risks.id, so if there are multiple rows in @risks then the query wont > return > any result. > > >> end >> >> Any help please. >> >> Many thanks. >> -- >> Posted via http://www.ruby-forum.com/. >> >> >Shiv, The @risks object will have multiple risks. What I am trying to do is for each risk I select I print the risk details then go and get any changes records associted with that risk record and print them then go round again for the next risk. Can you advise the syntax I would need to use to achieve this. Much appreciated Martyn>> > ~~ > Shiv N Gautam-- Posted via http://www.ruby-forum.com/. --~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the Google Groups "Ruby on Rails: Talk" group. To post to this group, send email to rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org To unsubscribe from this group, send email to rubyonrails-talk+unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en -~----------~----~----~----~------~----~------~--~---
On Sep 8, 11:24 am, Martyn Elmy-liddiard <rails-mailing-l...@andreas- s.net> wrote:> Shiv N Gautam wrote: > > On Fri, Sep 5, 2008 at 8:47 PM, Martyn Elmy-liddiard < > > rails-mailing-l...-ARtvInVfO7ksV2N9l4h3zg@public.gmane.org> wrote: > > >> @changes = Change.find_by_sql(["SELECT * FROM changes c where c.risk_id > >> = ? order by c.id", @risks.id]) > > > How many records are there in the @risks object. You''re doing > > c.risk_id=@ > > risks.id, so if there are multiple rows in @risks then the query wont > > return > > any result. > > >> end > > >> Any help please. > > >> Many thanks. > >> -- > >> Posted viahttp://www.ruby-forum.com/. > > Shiv, > > The @risks object will have multiple risks. What I am trying to do is > for each risk I select I print the risk details then go and get any > changes records associted with that risk record and print them then go > round again for the next risk. > > Can you advise the syntax I would need to use to achieve this.I wouldn''t use find_by_sql at all. owner = Owner.find params[:owner] Changes.find_all_by_risk_id owner.open_risks as long as owner has an open risks association like so has_many :open_risks, :conditions => {:status => ''Open''}, :class_name => ''Risk''} You could also do this with a named_scope. Fred> > Much appreciated > > Martyn > > > > > ~~ > > Shiv N Gautam > > -- > Posted viahttp://www.ruby-forum.com/.--~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the Google Groups "Ruby on Rails: Talk" group. To post to this group, send email to rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org To unsubscribe from this group, send email to rubyonrails-talk+unsubscribe@googlegroups.com For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en -~----------~----~----~----~------~----~------~--~---
I like using the ActiveRecord methods instead of raw sql, so here it
goed:
I assume that you have these 3 model:
class Owner < ActiveRecord::Base
has_many :risks
end
class Risk < ActiveRecord::Base
belongs_to :owner
has_many :changes
end
class Change < ActiveRecord::Base
belongs_to :risk
end
def reportbyowner2
@owner = Owner.find(params[:owner])
@risks = @owner.risks.all(:conditions => { :status =>
''Open''})
@risks.each do |risk|
risk.print_the_details
risk.changes.each{|change| change.print_the_details }
end
end
On Sep 8, 12:24 pm, Martyn Elmy-liddiard <rails-mailing-l...@andreas-
s.net> wrote:> Shiv N Gautam wrote:
> > On Fri, Sep 5, 2008 at 8:47 PM, Martyn Elmy-liddiard <
> > rails-mailing-l...-ARtvInVfO7ksV2N9l4h3zg@public.gmane.org> wrote:
>
> >> @changes = Change.find_by_sql(["SELECT * FROM changes c where
c.risk_id
> >> = ? order by c.id", @risks.id])
>
> > How many records are there in the @risks object. You''re
doing
> > c.risk_id=@
> > risks.id, so if there are multiple rows in @risks then the query wont
> > return
> > any result.
>
> >> end
>
> >> Any help please.
>
> >> Many thanks.
> >> --
> >> Posted viahttp://www.ruby-forum.com/.
>
> Shiv,
>
> The @risks object will have multiple risks. What I am trying to do is
> for each risk I select I print the risk details then go and get any
> changes records associted with that risk record and print them then go
> round again for the next risk.
>
> Can you advise the syntax I would need to use to achieve this.
>
> Much appreciated
>
> Martyn
>
>
>
> > ~~
> > Shiv N Gautam
>
> --
> Posted viahttp://www.ruby-forum.com/.
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk+unsubscribe@googlegroups.com
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en
-~----------~----~----~----~------~----~------~--~---
Thanks guys....your help was much appreciated! Martyn -- Posted via http://www.ruby-forum.com/. --~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the Google Groups "Ruby on Rails: Talk" group. To post to this group, send email to rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org To unsubscribe from this group, send email to rubyonrails-talk+unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en -~----------~----~----~----~------~----~------~--~---