im trying to use find_by_sql, i think its a very useful function, but im
having trouble using it.
in my outcomes table i have:
id status
1 pass
2 fail
3 pass
4 pass
in my controller im trying to set it like this:
@status = Outcome.find_by_sql "select status from outcomes where id =
1"
and in my outcome view
<body>
<%= @status %>
<%= "#{@status}" %>
</body>
when viewing the outcome.rhtml
it just appears as ''#''
ultimately i am not convinced the find_by_sql is working at all...?
do i need to ensure im using a certain gem, or version or rails?
RAILS 2.0.2
ruby 1.8.6 (2007-09-24 patchlevel 111) [i386-mswin32]
--
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 22 Sep 2008, at 14:31, Brad Symons wrote:> > im trying to use find_by_sql, i think its a very useful function, > but im > having trouble using it.Actually it is very rarely of use> > > in my outcomes table i have: > id status > 1 pass > 2 fail > 3 pass > 4 pass > > in my controller im trying to set it like this: > @status = Outcome.find_by_sql "select status from outcomes where id > = 1" >This would be better off as Outcome.find 1> and in my outcome view > > <body> > <%= @status %> > <%= "#{@status}" %> > </body> > > when viewing the outcome.rhtml > > it just appears as ''#'' > > ultimately i am not convinced the find_by_sql is working at all...?it is working. It''s just not what you think it is. @status is an array of Outcome objects. when you call to_s on an array (which is effectively what you''re doing with the <%= ), arrays just call to_s on their contents and join them. to_s on activerecord objects gives you something like #<Customer:0x2146688> which is invalid html (browsers frequently ignore everything between the <>). If you follow my suggestion and do @outcome = Outcome.find 1 then you could stick <%= h @outcome.status %> in your view (the h function escapes the text making it safe for html Fred --~--~---------~--~----~------------~-------~--~----~ 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 -~----------~----~----~----~------~----~------~--~---
Hi Fred thanks for your response, i tested your solution and it works fine. but its not quite as easy as ive posted above, the row im searching for in the outcomes table isnt always as row 1. so hardcoding a value in the controller is a no no. the outcomes table i am working with, looks more like this: +----+---------------------+-------------+------------+-----------+ | id | outcome_date | testcase_id | testrun_id | result_id | +----+---------------------+-------------+------------+-----------+ | 1 | 2008-09-22 11:51:28 | 1 | 16 | 4 | | 2 | 2008-09-22 12:23:10 | 1 | 26 | 1 | | 3 | 2008-09-22 12:31:00 | 1 | 26 | NULL | | 4 | 2008-09-22 13:25:44 | 1 | 26 | 3 | | 5 | 2008-09-22 13:29:52 | 1 | 26 | 2 | | 6 | 2008-09-22 13:33:33 | 1 | 26 | 2 | and i need to retrieve the most recent outcome_date for testcase_id 1, similarly i also need to retrieve the resulttype from the results table(shown below) by using the join on outcomes.result_id -> results.id, also for the most recent outcomes.outcome_date +----+------------+ | id | resulttype | +----+------------+ | 1 | Pass | | 2 | Fail | | 3 | N/A | | 4 | Not Run | +----+------------+ as you can see, a simple sql statement would be much more viable as an option, but as you state the find_by_sql isnt so useful, i am wondering how such querying can be resolved in rails. ive seen a variety of first, last, etc... but this seems like a lengthy way of doing things? and im not even sure it can be done. although i am a newbie... thanks for your help though. -- 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 22 Sep 2008, at 14:52, Brad Symons wrote:> > Hi Fred > > thanks for your response, i tested your solution and it works fine. > but > its not quite as easy as ive posted above, the row im searching for in > the outcomes table isnt always as row 1. > > so hardcoding a value in the controller is a no no. > > the outcomes table i am working with, looks more like this: > > +----+---------------------+-------------+------------+-----------+ > | id | outcome_date | testcase_id | testrun_id | result_id | > +----+---------------------+-------------+------------+-----------+ > | 1 | 2008-09-22 11:51:28 | 1 | 16 | 4 | > | 2 | 2008-09-22 12:23:10 | 1 | 26 | 1 | > | 3 | 2008-09-22 12:31:00 | 1 | 26 | NULL | > | 4 | 2008-09-22 13:25:44 | 1 | 26 | 3 | > | 5 | 2008-09-22 13:29:52 | 1 | 26 | 2 | > | 6 | 2008-09-22 13:33:33 | 1 | 26 | 2 | > > and i need to retrieve the most recent outcome_date for testcase_id 1, > similarly i also need to retrieve the resulttype from the results > table(shown below) by using the join on outcomes.result_id -> > results.id, also for the most recent outcomes.outcome_date > > +----+------------+ > | id | resulttype | > +----+------------+ > | 1 | Pass | > | 2 | Fail | > | 3 | N/A | > | 4 | Not Run | > +----+------------+ > > as you can see, a simple sql statement would be much more viable as an > option, but as you state the find_by_sql isnt so useful, i am > wondering > how such querying can be resolved in rails.I would probably have an appropriate association defined and do something like TestCase.find(1).outcomes.first (which will do what you want as long as your outcomes association has the appropriate order option) or Outcome.find_by_test_case_id(1, :order => ''outcome_date desc'') or Outcome.find :first, :conditions => {:test_case_id => 1}, :order => ''outcome_date desc'') I almost never use find_by_sql Fred> > > ive seen a variety of first, last, etc... but this seems like a > lengthy > way of doing things? and im not even sure it can be done. > > although i am a newbie... > > thanks for your help though. > -- > 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 -~----------~----~----~----~------~----~------~--~---
Hi Fred OK, I have managed to get the result_id to display, but how can i cross this over to giving me the resulttype from the results table? controller.rb @test1 = Outcome.find_by_testcase_id(@testcase, :order => ''outcome_date desc'') view.rhtml <%= h @test1.result_id %> i gather the above is ok, but how can i do a join, so i can gather the string(resulttype) if i can crack this, i will be able to pretty much do everything on tables and joins from here on... thanks for your kind help... -- 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 22 Sep 2008, at 15:31, Brad Symons wrote:> > Hi Fred > > OK, I have managed to get the result_id to display, but how can i > cross > this over to giving me the resulttype from the results table? > > controller.rb > @test1 = Outcome.find_by_testcase_id(@testcase, :order => > ''outcome_date > desc'') > > view.rhtml > <%= h @test1.result_id %> >You could do @test1.result.resulttype If you want AR to fetch that up front you can do> @test1 = Outcome.find_by_testcase_id(@testcase, :order => > ''outcome_date > desc'', :include => :result)Fred> i gather the above is ok, but how can i do a join, so i can gather the > string(resulttype) > > if i can crack this, i will be able to pretty much do everything on > tables and joins from here on... > > thanks for your kind help... > > -- > 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 -~----------~----~----~----~------~----~------~--~---
My advice is to drop find_by_sql. I used it to get the most performance from my Rails app. This worked in development environment. But surprisingly in production mode, the find_by_sql was barely faster than a usual find(). -- 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 -~----------~----~----~----~------~----~------~--~---
Fred no worries i DONE IT!! controller.rb: @test1 = Result.find_by_id(:joins => Outcome.find_by_testcase_id(@testcase, :order => ''outcome_date desc'')) view.rhtml: <%= h @test1.resulttype %> thanks for your help, i can now see find_by_sql is more of a liability than using the "filters" of the find command. great! thanks for your help! -- 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 -~----------~----~----~----~------~----~------~--~---
Fred Is there any way to do a count using the :find ... For example, supposing I want to list how many Pass, Fail etc But I only want to grab the number per testrun, per testcase with the most recent timestamp. So for example: +----+---------------------+-------------+------------+-----------+ | id | outcome_date | testcase_id | testrun_id | result_id | +----+---------------------+-------------+------------+-----------+ | 1 | 2008-09-22 11:51:28 | 1 | 16 | 4 | | 2 | 2008-09-22 12:23:10 | 1 | 26 | 1 | | 3 | 2008-09-22 12:31:00 | 1 | 26 | 2 | | 4 | 2008-09-22 13:25:44 | 2 | 26 | 3 | | 5 | 2008-09-22 13:29:52 | 3 | 26 | 2 | | 6 | 2008-09-22 13:33:33 | 4 | 26 | 2 | +----+------------+ | id | resulttype | +----+------------+ | 1 | Pass | | 2 | Fail | | 3 | N/A | | 4 | Not Run | +----+------------+ for testrun 16: 1x4 (No of N/A= 1) testrun 26: 3x2(tc_id=1,tc_id=3, tc_id=4), 1x3(tc_id=2) Num of Fail = 3, Num of N/A = 1, Num of Pass = 0, Num of Not run=4 -- 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 22 Sep 2008, at 16:29, Brad Symons wrote:> > Fred > > Is there any way to do a count using the :find ... > > For example, supposing I want to list how many Pass, Fail etc > > But I only want to grab the number per testrun, per testcase with the > most recent timestamp. >Have a play with Outcome.count. You can do stuff like Outcome.count :all, :joins => :testrun, :group => ''resulttype'' Fred> So for example: > +----+---------------------+-------------+------------+-----------+ > | id | outcome_date | testcase_id | testrun_id | result_id | > +----+---------------------+-------------+------------+-----------+ > | 1 | 2008-09-22 11:51:28 | 1 | 16 | 4 | > | 2 | 2008-09-22 12:23:10 | 1 | 26 | 1 | > | 3 | 2008-09-22 12:31:00 | 1 | 26 | 2 | > | 4 | 2008-09-22 13:25:44 | 2 | 26 | 3 | > | 5 | 2008-09-22 13:29:52 | 3 | 26 | 2 | > | 6 | 2008-09-22 13:33:33 | 4 | 26 | 2 | > > > +----+------------+ > | id | resulttype | > +----+------------+ > | 1 | Pass | > | 2 | Fail | > | 3 | N/A | > | 4 | Not Run | > +----+------------+ > > > for testrun 16: 1x4 (No of N/A= 1) > testrun 26: 3x2(tc_id=1,tc_id=3, tc_id=4), 1x3(tc_id=2) > Num of Fail = 3, Num of N/A = 1, Num of Pass = 0, Num of Not run=4 > > > > > -- > 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 -~----------~----~----~----~------~----~------~--~---