Bad sql after :conditions =>


#1

I’ve something like that:

in layout :
links looks that :

<%= link_to month.strftime(’%B, %Y’),
“/?y=#{month.strftime(”%Y")}&m=#{month.strftime("%m")}" %>

in controler I’ve :

if params[:y] && params[:m]
@posts = Post.find(:all,:order => ‘created_at’ ,:conditions =>
[‘extract(year from created_at) LIKE ? AND extract(month from
created_at) LIKE ?’, params[:y], params[:m]] )
else
@posts = Post.find(:all)
end

but in console I’ve got that sql code:
SELECT * FROM “posts” WHERE (extract(year from created_at) LIKE E’2009’
AND extract(month from created_at) LIKE E’01’) ORDER BY created_at

looks like this E near that LIKE broke everything

some idea how to repair that


#2

Oskar Szrajer wrote:

I’ve something like that:

in layout :
links looks that :

<%= link_to month.strftime(’%B, %Y’),
“/?y=#{month.strftime(”%Y")}&m=#{month.strftime("%m")}" %>

Don’t write a raw URI path and query, and don’t use string surgery to
create
one. Add those items as the arguments to url_for():

<%= link_to month.strftime(’%B, %Y’),
:y => month.year, :m => month.month %>

(BTW naming a date ‘month’ is kind’a tacky…)

That would escape your URI correctly, but this can’t be your problem
because
either way these are just numbers, not punctuation that needs escapes.

but in console I’ve got that sql code:
SELECT * FROM “posts” WHERE (extract(year from created_at) LIKE E’2009’
AND extract(month from created_at) LIKE E’01’) ORDER BY created_at

looks like this E near that LIKE broke everything

I can’t see where the E comes from, but why the LIKE? Your year and
month have
no % in them for LIKE to wildcard.

Can your “functional” test call this action correctly?


Phlip


#3

I think there will not a problem in link_to but I’ve try to use a
url_for

LIKE in condition is not a problem too I’ve check with = and still
getting something similary:

@posts = Post.find(:all,:order => ‘created_at’ ,:conditions =>
[‘extract(year from created_at) = ? AND extract(month from created_at) =
?’, params[:y], params[:m]] )

gives :

SELECT * FROM “posts” WHERE (extract(year from created_at) = E’2009’ AND
extract(month from created_at) = E’01’) ORDER BY created_at

still this E exist in sql code


#4

hmm after changing LIKE to =

in sql code is still "E "

but it’s start works, return proper page ?? now it’s big question for me
what that E is and whay it’s work now?


#5

ok I’ve got answer somewhere else :

http://www.postgresql.org/docs/8.3/interactive/sql-syntax-lexical.html#SQL-SYNTAX-IDENTIFIERS

this E is a part of postgres “”


#6

Oskar Szrajer wrote:

hmm after changing LIKE to =

in sql code is still "E "

but it’s start works, return proper page ?? now it’s big question for me
what that E is and whay it’s work now?

What is in the params = {}, in the output of your script/console when
you run that?


#7

Processing PostsController#index (for 127.0.0.1 at 2009-01-11 17:24:48)
[GET]
Parameters: {“m”=>“01”, “y”=>“2009”}
SQL (0.1ms) SET client_min_messages TO ‘panic’
SQL (0.1ms) SET client_min_messages TO ‘notice’
Post Load (4.5ms) SELECT * FROM “posts” WHERE (extract(year from
created_at) = E’2009’ AND extract(month from created_at) = E’01’) ORDER
BY created_at
Rendering template within layouts/application
Rendering posts/index
SQL (1.1ms) SELECT count() AS count_all FROM “comments” WHERE
(“comments”.post_id = 2)
CACHE (0.0ms) SELECT count(
) AS count_all FROM “comments” WHERE
(“comments”.post_id = 2)
Rendered posts/_post (23.8ms)
SQL (0.6ms) SELECT count() AS count_all FROM “comments” WHERE
(“comments”.post_id = 1)
CACHE (0.0ms) SELECT count(
) AS count_all FROM “comments” WHERE
(“comments”.post_id = 1)
Rendered posts/_post (4.5ms)
Post Load (13.5ms) SELECT * FROM “posts” ORDER BY created_at
Rendered arrays/_array (0.7ms)
Completed in 72ms (View: 43, DB: 20) | 200 OK
[http://127.0.0.1/?y=2009&m=01]