Fails => @groups = Group.find(:all, :order => "group")

"group" here is mistaken for a part of "GROUP BY". you can write

@groups = Group.find(:all, :order => '"group"')

for SQLite3, and awkward

@groups = Group.find(:all, :order => Group.connection.quote_column_name("group"))

for a more db agnostic application.

I can't imagine any way for activerecord to decide by itself if it needs to add quotation here. What if you need to order by "group" desc, or "other_table"."group", or lower("group") ?

In simple cases datamapper-like syntax :order => :group.desc could work, which isn't available for activerecord out of the box, unfortunatelly

I'm new to this (submitting a ticket) but will finish up with this:

These suggested alternative query styles:

@groups = Group.find(:all, :order => '"group"') @groups = Group.find(:all, :order => Group.connection.quote_column_name("group")) plus
"group" desc and even '"group" desc'

all worked for my code context rails ver, sqlite ver etc.

I'm no expert here but I am surprised active record had trouble parsing and setting up this query and while I haven't tried it, I assume it would have similar issues with "limit" :limit, "readonly" :readonly, "order :order, etc

Granted you're always on thin ice using names and symbols that may conflict with underlying code - perhaps the exception msg should be "invalid use of a keyword"?

Thank you for the quick reply - it was enlightening (for me) and saves me a col rename migration (:>

For anyone reading the previous update - "group" desc needs to be enclosed by either back ticks or by single quotes - my mistake for not knowing how to do a proper escape sequence. Let me try here with: `"group" desc` and '"group" desc'

this isn't a rails exception. it's a database exception, and activerecord has not much power against it, it just knows that error happened, and passes you whatever database says.

activerecord doesn't parse sql at all, it just tries to generate it correctly, and for some parts of query, like "order" part, it passes whatever you entered here without touching it.

and don't be fooled, different database engines use different pairs of quotes for table names, column names and strings, so if double quotes worked for you in sqlite and postgres, it doesn't mean they would work for mysql. and backticks do not work in postgres and sqlite, AFAIR. database driver DOES know, however, which qoutes are correct, and you can use ActiveRecord::Base.connection.quote_table_name and quote_column_name for that, and placeholders (?) for embedded strings.

I strongly suggest you keep looking to log/development.log while you learning activerecord, so you know what queries it generates under the hood. And you can google for recipe to how put sql log directly into your rails console output, this really helps.

omega watch