SQL error... :joins and :order in ActiveRecord find command

Hi guys!

There's a problem I'm dealing with since 2 days now and I haven't quite figured out what's wrong in my code.

Here it is...

Suppose I've got a pretty simple blogging app which has stories and comments. The comments belong to the stories...

Here's the code:

#app/model/story.rb class Story < ActiveRecord::Base   has_many :comments end

#app/model/comment.rb class Comment < ActiveRecord::Base   belongs_to :story end

#config/routes.rb ActionController::Routing::Routes.draw do |map|   map.resources :stories, :has_many => :comments   map.connect ':controller/:action/:id'   map.connect ':controller/:action/:id.:format' end

Now here's the problem...

Fetching records with the following command doesn't work:

Comment.find :all, :joins => :story, :conditions => {:stories => {:id => 1}}, :order => "created_at DESC"

It gives me the following error message:

ActiveRecord::StatementInvalid: SQLite3::SQLException: ambiguous column name: created_at: SELECT "comments".* FROM "comments" INNER JOIN "stories" ON "stories".id = "comments".story_id WHERE ("stories"."id" = 1) ORDER BY created_at DESC   from /Library/Ruby/Gems/1.8/gems/activerecord-2.3.5/lib/active_record/connection_adapters/abstract_adapter.rb:219:in `log'   from /Library/Ruby/Gems/1.8/gems/activerecord-2.3.5/lib/active_record/connection_adapters/sqlite_adapter.rb:172:in `execute'   from /Library/Ruby/Gems/1.8/gems/activerecord-2.3.5/lib/active_record/connection_adapters/sqlite_adapter.rb:417:in `catch_schema_changes'   from /Library/Ruby/Gems/1.8/gems/activerecord-2.3.5/lib/active_record/connection_adapters/sqlite_adapter.rb:172:in `execute'   from /Library/Ruby/Gems/1.8/gems/activerecord-2.3.5/lib/active_record/connection_adapters/sqlite_adapter.rb:320:in `select'   from /Library/Ruby/Gems/1.8/gems/activerecord-2.3.5/lib/active_record/connection_adapters/abstract/database_statements.rb:7:in `select_all_without_query_cache'   from /Library/Ruby/Gems/1.8/gems/activerecord-2.3.5/lib/active_record/connection_adapters/abstract/query_cache.rb:62:in `select_all'   from /Library/Ruby/Gems/1.8/gems/activerecord-2.3.5/lib/active_record/base.rb:661:in `find_by_sql'   from /Library/Ruby/Gems/1.8/gems/activerecord-2.3.5/lib/active_record/base.rb:1548:in `find_every'   from /Library/Ruby/Gems/1.8/gems/activerecord-2.3.5/lib/active_record/base.rb:615:in `find'   from (irb):1

What I've I done wrong?

Now, let's say i run the same command but omit to specify the :order statement just works fine!

Comment.find :all, :joins => :story, :conditions => {:stories => {:id => 1}} => [#<Comment id: 1, story_id: 1, body: "some text", created_at: "2010-04-24 23:10:48", updated_at: "2010-04-24 23:10:48">, #<Comment id: 2, story_id: 1, body: "some text 2", created_at: "2010-04-24 23:10:52", updated_at: "2010-04-24 23:10:52">, #<Comment id: 3, story_id: 1, body: "some text 3", created_at: "2010-04-24 23:10:55", updated_at: "2010-04-24 23:10:55">, #<Comment id: 4, story_id: 1, body: "some text 4", created_at: "2010-04-24 23:10:58", updated_at: "2010-04-24 23:10:58">]

I'm running Rails 2.3.5 and using as you guys can see, SQLite3 for development. By the way, everything works fine with MySQL...

Need your help guys,

Thx a lot


That error is telling you everything you need to know - there is more than one "created_at" column in your query's selected fields. So you need to specify which one to use for the order:

  Comment.find :all, :joins => :story, :conditions => {:stories => {:id => 1}}, :order => "comments.created_at DESC"

Hope that helps.

  Comment.find :all, :joins => :story, :conditions => {:stories => {:id => 1}}, :order => "comments.created_at DESC"

Hope that helps.

Thx a lot @Michael!! You saved the day!