Issue with Rails 2.3.5 Active Record Conditions for IN

Question

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?

Answer 1

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)