0

I am having a issue using active record conditions in rails 2 with postgres. My problem is when ever I try to use this query (I am using geokit, but that is not the issue)

Table.all(:conditions => ["id IN (?)", id_array.join(",")], :origin => origin, :within => 20)

I get this problem:

ActiveRecord::StatementInvalid: PG::Error: ERROR:  invalid input syntax for integer: "21,28"
LINE X: ...OM "tables" WHERE (((id IN (E'21,28...

Why is it putting the "E'" in there and how to I get rid of it?

Red
  • 2,256
  • 5
  • 25
  • 38

5 Answers5

3

With id_array.join(","), you pass a single string to the function. As all strings get escaped and surrounded by single quotes when put into the generated SQL query, you naturally get the SQL error, as in this IN statement you can only use comma-separated integers.

The simple solution is to directly pass an array to the function. ActiveRecord then creates the correct values in SQL on its own. You can use it like this:

Table.all(:conditions => ["id IN (?)", id_array], :origin => origin, :within => 20)

Alternatively, you could use this even more ruby-like syntax which produces the exact same SQL query:

Table.all(:conditions => {:id => id_array}, :origin => origin, :within => 20)
Holger Just
  • 52,918
  • 14
  • 115
  • 123
1

You have already been directed to a different path. But to answer your question:

Why is it putting the "E'" in there ...

That is the escape string syntax of PostgreSQL. The default behaviour of PostgreSQL has changed with version 9.1, backslash-escapes are no longer interpreted, unless there is an E in front of the opening single quote. The new behaviour reflects the SQL standard.

 'one backslash: \'  -- backslash has no special meaning in Postgres 9.1
E'one backslash: \\' -- must use E'' syntax to interpret escape sequences

Some clients play it safe and put the E in front of every string literal to get the same behaviour regardless of the PostgreSQL version or the setting of standard_conforming_strings in particular. Others ask the server about the setting and adapt the syntax accordingly. Either way, it is just a waste of (very few) CPU cycles to put an E in front of a string literal that doesn't contain a backslash.

Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
0

I dont think u need to join the values. even in Rails 2. That been said, try this out:

Table.all(:conditions => ["id IN (?)", id_array], :origin => origin, :within => 20)
Arthur Neves
  • 11,840
  • 8
  • 60
  • 73
0

Try this instead:

Table.all(:conditions => ["id IN (#{id_array.join(",")})"], :origin => origin, :within => 20
cdesrosiers
  • 8,862
  • 2
  • 28
  • 33
0

I had the same issue with mysql trying to run count() function and syntax i used to fix it was

Issue.all(:conditions => "status_id IN (5, 15, 19, 21, 25)").count
Djikiné
  • 21
  • 5