Complicated finds are eating my sole

Rails
1

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

2

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

3

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 S.
http://www.railtie.net/

4

On 2/8/06, Craig W. [email protected] 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.

5

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

6

On Wed, 2006-02-08 at 21:52 -0600, Steve O. 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

7

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

8

On Wed, 2006-02-08 at 23:07 -0500, Wilson B. 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? :wink:

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

9

On Wed, 2006-02-08 at 21:06 -0800, Ben M. 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

10

On 9 Feb 2006, at 08:38, Mikkel B. 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

11

On Thu, 2006-02-09 at 08:38 +0000, Mikkel B. 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

12

Craig W. 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

13

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 B.

www.strongside.dk - Football Portal(DK)
nflfeed.helenius.org - Football News(DK)
ting.minline.dk - Buy Old Stuff!(DK)

14

Sorry to hear about your feet. Or is it your fish they’re eating? :wink:

Best regards,
Bill

----- Original Message -----
From: “Craig W.” [email protected]
To: [email protected]
Sent: Wednesday, February 08, 2006 9:43 PM
Subject: [Rails] complicated finds are eating my sole

15

On Thu, 2006-02-09 at 17:44 +0100, Kevin O. 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

16

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…

:wink:

17

|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

|

[email protected]
http://lists.rubyonrails.org/mailman/listinfo/rails

Rails mailing list
[email protected]
http://lists.rubyonrails.org/mailman/listinfo/rails

–
Eric G.
http://www.ericgoodwin.com

18

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