Problem with :conditions in find

I’m having the strangest problem…

I’m getting syntax errors on this find…

@lob_messages = ProviderMessage.find(:all, :conditions => [ “to = ?”, “GFA”], :order => ‘created_at DESC’, :limit => 5)

The Exception Rails is returning is:
Mysql::Error: #42000You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'to = 'GFA') ORDER BY created_at DESC LIMIT 5' at line 1: SELECT * FROM provider_messages WHERE (to = 'GFA') ORDER BY created_at DESC LIMIT 5
I really would appreciate another set of eyeballs. Anybody see anything wrong with the find?
And I can't get a find with :conditions => [ "something IN (?)", some_array] to work either.
Any assistance would really be appreciated!
TIA,
Bill

I’m having the strangest problem…

I’m getting syntax errors on this find…

@lob_messages = ProviderMessage.find(:all, :conditions => [ “to = ?”, “GFA”], :order => ‘created_at DESC’, :limit => 5)

The Exception Rails is returning is:
Mysql::Error: #42000You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'to = 'GFA') ORDER BY created_at DESC LIMIT 5' at line 1: SELECT * FROM provider_messages WHERE (to = 'GFA') ORDER BY created_at DESC LIMIT 5
I really would appreciate another set of eyeballs. Anybody see anything wrong with the find?

It seems “to” is a reserved word in mysql, check the manual yourself.

And I can't get a find with :conditions => [ "something IN (?)", some_array] to work either.

use some_array.joins(“,”) instead.

Ye Dingding wrote:

It seems "to" is a reserved word in mysql,

Thanks. It never occurred to me that it might be a reserved word problem.

use some_array.joins(",") instead.

And thanks again!

Best regards, Bill

While I’d strongly suggest that you change the name of that column, if you actually managed to create a column with that name, you should be able to add ID quotes (that is, backticks or grave accents) to the column name:

:conditions => [ “to = ?”, “GFA”],

-Rob

Rob Biedenharn http://agileconsultingllc.com

Rob@AgileConsultingLLC.com

Hi Rob,

Rob Biedenharn wrote:

While I'd strongly suggest that you change the name of that column,

Did that.

if you actually managed to create a column with that name,

Did. Via a migration.

you should be able to add ID quotes (that is, backticks or grave accents) to the column name:

Thanks for that information. When I get some time, I'll go back and see if that works.

Thanks again! Bill :conditions => [ "`to` = ?", "GFA"],

-Rob

Rob Biedenharn http://agileconsultingllc.com Rob@AgileConsultingLLC.com

Rob, Would you mind taking this one step further and show how I'd write the condition to find an integer instead of a string. I get the same error message described above when I try to make the :condition for an integer like this; :conditions => [ 'intfieldvalue = ?, intvalue'] surprisingly, it passes with this, but the condition is disregared for some reason? :conditions => [ 'intfieldvalue = intvalue'] I am grateful for a reply. Kathy KathysKode@gmail.com

Hi --

Rob, Would you mind taking this one step further and show how I'd write the condition to find an integer instead of a string. I get the same error message described above when I try to make the :condition for an integer like this; :conditions => [ 'intfieldvalue = ?, intvalue']

That has to be:

   :conditions => ['intfieldvalue = ?', intvalue]

The values to be interpolated have to be outside the string, as separate array elements.

surprisingly, it passes with this, but the condition is disregared for some reason? :conditions => [ 'intfieldvalue = intvalue']

If you want it all in one string (usually not advisable, for security reasons), you'd have to do Ruby-style string interpolation:

   :conditions => "intfieldvalue = #{intvalue}"

David