I am getting inconsistent results when trying to pass an array as a
find condition (using SQL WHERE...IN...). My code generates a
StatementInvalid error unless execution is interrupted by the
debugger, in which case the array is passed as expected (when
execution resumes after issuing the 'cont' command).
#application.rb
def current_organisation_units
current_user.organisation_unit_contacts.map {|o|
[o.organisation_unit_id]}
# debugger # When this line is commented out, execution fails;
when this line is enabled, execution succeeds.
end
#people_controller.rb
def index
@people = Person.find(:all, :conditions =>
["clients.organisation_unit_id IN (?)",
current_organisation_units], :include => :clients)
end
The error (note the DBMS is Postgres):
ActiveRecord::StatementInvalid (PGError: ERROR: invalid input syntax
for integer: "---
- 1
The SQL that is generated:
...WHERE (clients.organisation_unit_id IN (E'---
- 1
')
With the debugger line in application.rb enabled, the following SQL is
generated:
...WHERE (clients.organisation_unit_id IN (1))
This works as expected.
def index
@people = Person.find(:all, :conditions =>
["clients.organisation_unit_id IN (?)",
current_organisation_units], :include => :clients)
end
The error (note the DBMS is Postgres):
ActiveRecord::StatementInvalid (PGError: ERROR: invalid input syntax
for integer: "---
- 1
the error above want say that current_organisation_units is saved in
YAML version, current_organisation_units is array if it is saved in
DATABASE it will be YAML version like "--- 1 " but if it is parameter
array, it will be string "1", the matter now you should convert
current_organisation_units to array back.
try using:
YAML.load(current_organisation_units)
I dont know "current_organisation_units" come from. is it parameter or
from table. so i only argue like above. I am not sure that if your array
contains many element, your conditions in .find will work.
just come back if you get error. I hope my opinion can help you.
Thanks for you reply. Current_organisation_units is a method (not from
a table) that uses current_user (from the restful_authentication
plugin). My main issue is that the behaviour is correct if I interrupt
execution with the debugger, but incorrect without the debugger.
Although it would be interesting to find out what the debugger does
that causes this difference, I'm mostly concerned with getting my code
working without the debugger, of course.
Thanks for you reply. Current_organisation_units is a method (not from
a table) that uses current_user (from the restful_authentication
plugin). My main issue is that the behaviour is correct if I interrupt
execution with the debugger, but incorrect without the debugger.
Although it would be interesting to find out what the debugger does
that causes this difference, I'm mostly concerned with getting my code
working without the debugger, of course.
well having debugger on the last line of a function will change the
return value of the function (since a functions return value is the
value of the last statement). Why it changes it in any particular way
is anyone's guess
But your problem here is
current_user.organisation_unit_contacts.map {|o|
[o.organisation_unit_id]}
This will return something like [[1], [23], [485]] rather than
[1,23,485]
Whoops. Thanks for spotting this for me, Fred. It's working now that
I've changed that line to:
current_user.organisation_unit_contacts.map {|o|
o.organisation_unit_id}
