Illegal MySQL in #to_sql and #to_hash

DB is MySQL.

To illustrate:

I'm expecting the result of

FormalResult.find(:all, :conditions => {'module_name' =>"comfy"})

to be the same as:

FormalResult.find(:all, :conditions => {'module_name' =>"comfy"})

But the SQL emitted looks significantly different:

SELECT * FROM DM19_FORMAL WHERE (DM19_FORMAL.`module_name`='comfy')

and

SELECT * FROM DM19_FORMAL WHERE ('module_name' = 'comfy')

Raw ActiveRecord#find gets the quoting right, with backticks.

Given your definition of #sanitize above I tried:

>> ActiveRecord::Base.connection.quote(:symbol)

=> "'--- :symbol\\n'"

>> ActiveRecord::Base.connection.quote('string')

=> "'string'"

So it may be that the fact that am fiddling with the database connection (due to my legacy database arrangement) is causing a problem.

I'm not sure where to look next.

Presumably quoting strings works OK in a 'standard' database arrangement?

I'd revert to using symbols to identify column names but I have some columns that look like name1.name2 and they need to be quoted.

As an aside, in the sanitize method you show, I see there's a when clause for a Regexp. I'm not familiar with the code but when would you see a Regexp on the left hand side of an expression that would need to be quoted?

Can you post some of the ActiveRecord code you had to write in order to get your legacy DB working? set_table_name and all that? Ideally I'd like to add it to our test suite -- no reason Ambition shouldn't work for you

As for the Regexp, sanitize (I believe) is used on the right and left side of the WHERE condition. So if you're using MySQL, you want to turn /chris/i into 'chris'

On reflection, I'm not sure it's got much to do with changing table names, because that part seems to be correct:

From script/console:

>> Project.table_name

=> "ProjectsDB.projects"

This just shows that I have an AR model defined:

class Project < ActiveRecord::Base
  set_table_name "ProjectsDB.projects"
end

Some experiments -

>> Project.select{|z| z.column == 'value'}.to_sql

=> "SELECT * FROM ProjectsDB.projects WHERE ProjectsDB.projects.`column` = 'value'"

The above shows that the table name is correctly used in the SQL.

>> Project.select{:column == 'value'}.to_sql

=> "SELECT * FROM ProjectsDB.projects WHERE column = 'value'"

The above shows the table name is still used, but not in the WHERE clause.

>> Project.select{'column' == 'value'}.to_sql

=> "SELECT * FROM ProjectsDB.projects WHERE 'column' = 'value'"

This shows that when I use a string to identify the column name it is interpreted as a string constant and not as a column name. My expectation was that there would be a 'column'.to_sym call (or maybe a :column.to_s call) somewhere along the line and identifying a column by string or symbol would be identical.

=======================

As an aside, the syntax I'd really like to use is:

Project.select{ associatedmodel.column == 'value' }

Where the model looks like:

class Project < ActiveRecord::Base
  has_many :associatedmodel
end

Now I know that the select{} above doesn't work, so the syntax:

Project.select{ 'associatedmodel.column' == 'value' }

is a reasonable alternative.

I would like to offer the syntax to users in a DSL. I feel that writing an expression like:

select{ 'associatedmodel.column' == 'value' }

is more natural to my users than asking them to write:

select{|z| z.associatedmodel.column == 'value' }

I think the issue may have been solved:

>> Project.select{'column' == 'value'}.to_sql
=> "SELECT * FROM ProjectsDB.projects WHERE column = 'value'"

which shows that the column name, being passed in as a string, is correctly inserted into the SQL query.