Forum: Ruby on Rails complicated finds are eating my sole

Announcement (2017-05-07): www.ruby-forum.com is now read-only since I unfortunately do not have the time to support and maintain the forum any more. Please see rubyonrails.org/community and ruby-lang.org/en/community for other Rails- und Ruby-related community platforms.
C1e5a9e9344b6d31b9df7303e6dc378a?d=identicon&s=25 Craig White (Guest)
on 2006-02-09 04:43
(Received via mailing list)
I abandoned ruby way for find_by_sql and still can't get this...

@myplacement = Placement.find_by_sql(
"select * from placement where
 :intake_date >= beg_intake_date
and
 :intake_date <= end_intake_date")

just a simple date range...it's killing me - If I knew how to load
placement controller into irb, I could probably try out finds
interactive mode...

Thanks

Craig
E98de78bb42013c488fe8aa9d77d2cb1?d=identicon&s=25 Steve Odom (Guest)
on 2006-02-09 04:53
(Received via mailing list)
Hi Craig,

What helps me when I have to resort to a find_by_sql for a hairy query
is to
set it up like:

sql = "select *from placement where
:intake_date >= beg_intake_date
and
:intake_date <= end_intake_date")

breakpoint
find_by_sql(sql)

With the breakpoint there I can see what my sql statement will look like
once the variables are populated. Then I take that statement over to my
database front-end client and run it so I can see if it works directly
in
my_sql or whatever.

But I'm sure if you work at it, you can probably use a regular find with
:conditions.

Steve
http://www.smarkets.net
Af93ba6b6b59f22a8f37e8de5702ef98?d=identicon&s=25 Bob Silva (Guest)
on 2006-02-09 04:53
(Received via mailing list)
Try this:

@myplacement = Placement.find_by_sql(
["select * from placement where intake_date >= ?
and intake_date <= ?", begin_intake_date, end_intake_date])

Assuming intake_date is the name of your field and {beg|end}_intake_date
are
local variables or methods of your model.

Bob Silva
http://www.railtie.net/
25e11a00a89683f7e01e425a1a6e305c?d=identicon&s=25 Wilson Bilkovich (Guest)
on 2006-02-09 05:07
(Received via mailing list)
On 2/8/06, Craig White <craigwhite@azapple.com> wrote:
> interactive mode...
@placements = Placement.find :all, :conditions => ["intake_date
between ? and ?", begin_date, end_date]

..where begin_date and end_date are the values you'd like to populate
the query with.
If this is a controller action, they are probably in the params, etc,
etc.

find :all and find_by_sql both return all matches, so remember that
@placements is likely to be an Array of Placement instances.
C1e5a9e9344b6d31b9df7303e6dc378a?d=identicon&s=25 Craig White (Guest)
on 2006-02-09 05:27
(Received via mailing list)
On Wed, 2006-02-08 at 21:52 -0600, Steve Odom wrote:
> breakpoint
> find_by_sql(sql)
>
> With the breakpoint there I can see what my sql statement will look
> like once the variables are populated. Then I take that statement over
> to my database front-end client and run it so I can see if it works
> directly in my_sql or whatever.
>
> But I'm sure if you work at it, you can probably use a regular find
> with :conditions.
>
----
I couldn't figure out how to do the 'conditions' with a date
range...definitely couldn't find anything like that in Agile book and
even scoured through Pickaxe for a hint.

Now the breakpoint thing is something that would be worthwhile knowing.

I can't seem to make that work. I am looking through that p195/196 and
it suggests that I use the 'method breakpoint()'

I have tried inserting..

sql = "select * ...
breakpoint() #also breakpoint
find_by_sql(sql)

in the controller and also tried in the rhtml page that I am loading -
inserting it before the part where it errors and it blows right through
the appropriate location in the controller method and in the rhtml page.

so I can't make that work

I have a console open running breakpointer - waiting for the bus...but I
guess the bus is at Disneyworld.

Craig
071aec714fe5f3fe5b1df9fc5adb45f2?d=identicon&s=25 Raghuraman Suraj (Guest)
on 2006-02-09 05:36
(Received via mailing list)
Hi Craig,
if ur concern is only to look at the query which is fired then look into
the log/development.log file. Break point is generally setup on the
controller only & the way u have set it up seems to be correct, are u
developing on ur local system or a remote machine( if remote then check
the firewall settings) coz breakpointer uses a different port( i forgot
the no.) & u should allow connections to that.

Suraj
4005a47a8f2ceee49670b920593c1d52?d=identicon&s=25 Ben Munat (Guest)
on 2006-02-09 06:04
(Received via mailing list)
I remember having some trouble figuring out the breakpoint thing... I
don't remember what.
But it works for me now... and I just did one in a controller and one in
an rhtml file
(inside a <% %>)... both worked.

Here's what I did...

I put "breakpoint" in an action inside a controller:

   def list
     breakpoint
     @proposal_pages, @proposals = paginate :proposals, :per_page => 10
   end

and started webrick.

Then I opened a terminal window changed to my project directory and ran
"ruby
script\breakpointer". It said something about it'd be checking for
connections every 2
seconds.

Then I opened a browser and invoked the controller with the breakpoint
and the terminal
window said something new (sorry, I forgot what) and gave me a ">". I
typed "inspect" and
got a ton of crap... I typed "methods" and got some more crap... I typed
"@session.inspect" and got all the stuff in the session...

Hope that helps some how!

b
C1e5a9e9344b6d31b9df7303e6dc378a?d=identicon&s=25 Craig White (Guest)
on 2006-02-09 07:26
(Received via mailing list)
On Wed, 2006-02-08 at 23:07 -0500, Wilson Bilkovich wrote:
> > placement controller into irb, I could probably try out finds
> @placements is likely to be an Array of Placement instances.
----
this has turned out to be a real bugger...and I've has some great advice
from some very smart people (where's Estelle when I need her?  ;-)

as you can see by the log...the data is there but it certainly is
delivering a 'null' in the SQL Select command...

Processing PlacementsController#findresult (for 127.0.0.1 at 2006-02-08
23:13:22) [POST]
  Parameters: {"commit"=>"Find", "action"=>"findresult",
"controller"=>"placements", "placement"=>{"beg_referral_date"=>"",
"end_referral_date"=>"", "beg_discharge_date"=>"",
"end_intake_date"=>"01/01/2007", "end_discharge_date"=>"",
"beg_intake_date"=>"01/01/2001"}}

(separation here for readability)

  Placement Count (0.002219)   SELECT COUNT(*) FROM placements WHERE
(intake_date between NULL and NULL)
  Placement Load (0.009762)   SELECT * FROM placements WHERE
(intake_date between NULL and NULL) LIMIT 14 OFFSET 0
Rendering  within layouts/placements
Rendering placements/findresult
Completed in 0.05280 (18 reqs/sec) | Rendering: 0.00669 (12%) | DB:
0.01198 (22%) | 200 OK [http://localhost/placements/findresult]
[craig@srv2 th-db]$

Is it because it rails doesn't recognize "01/01/2001" as a date?

Select * FROM placements WHERE (intake_date between NULL and NULL)

in case it offers clues...this is how the section goes...

  def findresult

    beg_intake_date    = params[:beg_intake_date]
    end_intake_date    = params[:end_intake_date]
    beg_referral_date  = params[:beg_referral_date]
    end_referral_date  = params[:end_referral_date]
    beg_discharge_date = params[:beg_discharge_date]
    end_discharge_date = params[:end_discharge_date]


    if beg_intake_date != "" then
      @placement_pages, @placements = paginate(
        :placements,
        :conditions => ["intake_date between ? and ?",
          beg_intake_date, end_intake_date],
        :per_page => 14)
    elsif beg_referral_date != "" then

# I'm sure that I don't have to keep repeating the pattern here...

This has taken up the WHOLE evening - ouch

Craig
C1e5a9e9344b6d31b9df7303e6dc378a?d=identicon&s=25 Craig White (Guest)
on 2006-02-09 07:32
(Received via mailing list)
On Wed, 2006-02-08 at 21:06 -0800, Ben Munat wrote:
>      @proposal_pages, @proposals = paginate :proposals, :per_page => 10
> got a ton of crap... I typed "methods" and got some more crap... I typed
> "@session.inspect" and got all the stuff in the session...
>
> Hope that helps some how!
---
interesting 'technical' description of the process...I ***think*** I
understand. I have been focused on cleaning up the find by dates thing
which seems so simple but is clearly beyond my reach. I do want to
understand this though...

Thanks

Craig
5d15c6821f3c3054c04b85471824ba7c?d=identicon&s=25 Mikkel Bruun (Guest)
on 2006-02-09 09:38
(Received via mailing list)
>---
>interesting 'technical' description of the process...I ***think*** I
>understand. I have been focused on cleaning up the find by dates thing
>which seems so simple but is clearly beyond my reach. I do want to
>understand this though...
>
>Thanks
>
>Craig

Complicated finds should really be placed in the model...

Try


(6.months.ago.to_date..1.year.ago.to_date).to_s(:db)

Which produces:


=> "BETWEEN '2005-07-28' AND '2005-01-23'"

Courtesy of http://www.jeremydurham.com/blog/post/6

Mikkel Bruun

www.strongside.dk    - Football Portal(DK)
nflfeed.helenius.org - Football News(DK)
ting.minline.dk      - Buy Old Stuff!(DK)
Fd1769776698da69ffd5bdda094d8581?d=identicon&s=25 Jon Evans (Guest)
on 2006-02-09 11:07
(Received via mailing list)
On 9 Feb 2006, at 08:38, Mikkel Bruun wrote:

> Try
>
> (6.months.ago.to_date..1.year.ago.to_date).to_s(:db)
>
> Which produces:
>
> => "BETWEEN '2005-07-28' AND '2005-01-23'"
>
> Courtesy of http://www.jeremydurham.com/blog/post/6

Brilliant!

Thanks Mikkel, and Jeremy.

Jon
C1e5a9e9344b6d31b9df7303e6dc378a?d=identicon&s=25 Craig White (Guest)
on 2006-02-09 17:37
(Received via mailing list)
On Thu, 2006-02-09 at 08:38 +0000, Mikkel Bruun wrote:
> Complicated finds should really be placed in the model...
>
----
that makes sense but I think that my problem is dealing with an existing
string that looks like a date but isn't and I'm getting strange results
when trying to convert it...

NoMethodError in Placements#findresult
You have a nil object when you didn't expect it!
The error occured while evaluating nil.to_date

Request
Parameters: {"commit"=>"Find", "placement"=>{"beg_referral_date"=>"",
"end_referral_date"=>"", "beg_discharge_date"=>"",
"end_intake_date"=>"2007-01-01", "end_discharge_date"=>"",
"beg_intake_date"=>"2001-01-01"}}

  def findresult

    beg_in_date = params[:beg_intake_date]

    beg_intake_date    = beg_in_date.to_date # line that causes error

It seems that beg_in_date is null - even though you can see that the
Parameters passed show a value in beg_intake_date

Why is this happening?

Craig
C8a634a01a2c4508360874bff7fb1a7f?d=identicon&s=25 Kevin Olbrich (olbrich)
on 2006-02-09 17:44
Craig White wrote:
> Request
> Parameters: {"commit"=>"Find", "placement"=>{"beg_referral_date"=>"",
> "end_referral_date"=>"", "beg_discharge_date"=>"",
> "end_intake_date"=>"2007-01-01", "end_discharge_date"=>"",
> "beg_intake_date"=>"2001-01-01"}}
>
>   def findresult
>
>     beg_in_date = params[:beg_intake_date]
>
>     beg_intake_date    = beg_in_date.to_date # line that causes error
>
> It seems that beg_in_date is null - even though you can see that the
> Parameters passed show a value in beg_intake_date

Try this..

beg_intake_date = params["placement"]["beg_intake_date"]

_Kevin
C1e5a9e9344b6d31b9df7303e6dc378a?d=identicon&s=25 Craig White (Guest)
on 2006-02-09 17:58
(Received via mailing list)
On Thu, 2006-02-09 at 17:44 +0100, Kevin Olbrich wrote:
> >
> >     beg_intake_date    = beg_in_date.to_date # line that causes error
> >
> > It seems that beg_in_date is null - even though you can see that the
> > Parameters passed show a value in beg_intake_date
>
> Try this..
>
> beg_intake_date = params["placement"]["beg_intake_date"]
----
awesome - thanks

Craig
2b891e820c238ded365d035771603f21?d=identicon&s=25 Bill Walton (Guest)
on 2006-02-09 19:14
(Received via mailing list)
Sorry to hear about your feet.  Or is it your fish they're eating?  ;-)

Best regards,
Bill

----- Original Message -----
From: "Craig White" <craigwhite@azapple.com>
To: <rails@lists.rubyonrails.org>
Sent: Wednesday, February 08, 2006 9:43 PM
Subject: [Rails] complicated finds are eating my sole
Ac1da5d66d51a1c3b843221032247b30?d=identicon&s=25 Eric Goodwin (Guest)
on 2006-02-09 19:39
(Received via mailing list)
|Hi,

You can do ranges with this nice little rails date helper
#Date Range
(Date.today..1.year.ago.to_date).to_s(:db) #=> "BETWEEN '2006-01-24' AND
'2005-01-24'"

So that leave us with:

between = ||(Date.today..1.year.ago.to_date).to_s(:db)
intake_date = intake_date.to_s(:db)

|@myplacement = Placement.|find_by_sql("SELECT * from placement WHERE #{||intake_date||} 
#{||between}"||)

I haven't tested this specific code, but it should work.

Eric

|

>>
>> Rails@lists.rubyonrails.org
>> http://lists.rubyonrails.org/mailman/listinfo/rails
>>
> _______________________________________________
> Rails mailing list
> Rails@lists.rubyonrails.org
> http://lists.rubyonrails.org/mailman/listinfo/rails
>


--
Eric Goodwin
http://www.ericgoodwin.com
C1e5a9e9344b6d31b9df7303e6dc378a?d=identicon&s=25 Craig White (Guest)
on 2006-02-09 20:38
(Received via mailing list)
Just another demonstration of my capacity to put my foot in my mouth.

Obviously I was so flabbergasted by then, homonyms/malapropisms were the
least of my issues - thanks to the list are now resolved - notice - no
questions for hours now...

;-)
C1e5a9e9344b6d31b9df7303e6dc378a?d=identicon&s=25 Craig White (Guest)
on 2006-02-09 20:43
(Received via mailing list)
Thanks...one of the problems with 'find_by_sql' method is invalid
searches give no warning to me where ruby methods do - I actually got a
working ruby method and in the process...I have learned A LOT and now am
much clearer on how things work in rails.

It took some time and some really bad hacks to get me this far but now I
am going back and cleaning up some of the stupid stuff I did that worked
in favor of clean stuff because I now have a better grip on things and
can keep things a bit more 'DRY'

Obviously one of the things that always throws me for a loop is when to
use []  () and {} or simply not to use them at all...which has a
profound effect on anything in my controllers related to searches.

One thing that I get now...
I really like <% render :partial ... %>
That has really simplified things a lot

Craig
This topic is locked and can not be replied to.