This project is archived and is in readonly mode.
Fails => @groups = Group.find(:all, :order => "group")
Reported by dennismajor1 (at gmail) | February 10th, 2010 @ 03:55 AM
@groups = Group.find(:all, :order => "group") fails
@groups = Group.find(:all, :order => "id") does not fail
sqlite3-ruby 1.2.1
Rails: 2.3.5
Migration File:
class CreateGroups < ActiveRecord::Migration
def self.up
create_table :groups do |t|
t.string :group
t.timestamps
end
end
def self.down
drop_table :groups
end end
Session Dump:
ActiveRecord::StatementInvalid in GroupsController#index
SQLite3::SQLException: near "group": syntax error: SELECT * FROM
"groups" ORDER BY group
RAILS_ROOT: C:/rails/RM/MajorArtistry
Application Trace | Framework Trace | Full Trace
C:/rails/RM/MajorArtistry/vendor/rails/activerecord/lib/active_record/connection_adapters/abstract_adapter.rb:219:in
log'<br/>
C:/rails/RM/MajorArtistry/vendor/rails/activerecord/lib/active_record/connection_adapters/sqlite_adapter.rb:172:inexecute'
C:/rails/RM/MajorArtistry/vendor/rails/activerecord/lib/active_record/connection_adapters/sqlite_adapter.rb:417:in
catch_schema_changes'<br/>
C:/rails/RM/MajorArtistry/vendor/rails/activerecord/lib/active_record/connection_adapters/sqlite_adapter.rb:172:inexecute'
C:/rails/RM/MajorArtistry/vendor/rails/activerecord/lib/active_record/connection_adapters/sqlite_adapter.rb:320:in
select'<br/>
C:/rails/RM/MajorArtistry/vendor/rails/activerecord/lib/active_record/connection_adapters/abstract/database_statements.rb:7:inselect_all_without_query_cache'
C:/rails/RM/MajorArtistry/vendor/rails/activerecord/lib/active_record/connection_adapters/abstract/query_cache.rb:60:in
select_all'<br/>
C:/rails/RM/MajorArtistry/vendor/rails/activerecord/lib/active_record/connection_adapters/abstract/query_cache.rb:81:incache_sql'
C:/rails/RM/MajorArtistry/vendor/rails/activerecord/lib/active_record/connection_adapters/abstract/query_cache.rb:60:in
select_all'<br/>
C:/rails/RM/MajorArtistry/vendor/rails/activerecord/lib/active_record/base.rb:661:infind_by_sql'
C:/rails/RM/MajorArtistry/vendor/rails/activerecord/lib/active_record/base.rb:1548:in
find_every'<br/>
C:/rails/RM/MajorArtistry/vendor/rails/activerecord/lib/active_record/base.rb:615:infind'
C:/rails/RM/MajorArtistry/app/controllers/groups_controller.rb:4:in
index'
Comments and changes to this ticket
-
codesnik February 10th, 2010 @ 08:26 AM
"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", orlower("group")?In simple cases datamapper-like syntax
:order => :group.desccould work, which isn't available for activerecord out of the box, unfortunatelly -
dennismajor1 (at gmail) February 10th, 2010 @ 01:09 PM
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" descand 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 (:>
-
dennismajor1 (at gmail) February 10th, 2010 @ 01:20 PM
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'
-
codesnik February 10th, 2010 @ 02:07 PM
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.
-
Rohit Arondekar June 15th, 2010 @ 12:16 PM
- State changed from “new” to “invalid”
Closing ticket. Feel free to make a comment asking to reopen it and I'll reopen it.
-
Ryan Bigg October 9th, 2010 @ 09:49 PM
- Tag cleared.
- Importance changed from “” to “Low”
Automatic cleanup of spam.
-
Create your profile
Help contribute to this project by taking a few moments to create your personal profile. Create your profile »
<h2 style="font-size: 14px">Tickets have moved to Github</h2>
The new ticket tracker is available at <a href="https://github.com/rails/rails/issues">https://github.com/rails/rails/issues</a>
dennismajor1 (at gmail)
codesnik
Jeremy Kemper
Rohit Arondekar
Ryan Bigg