Rails - Apr 2010 - 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

LP
-- 
Posted via http://www.ruby-forum.com/.

-- 
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk+unsubscribe-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en.

On 25 April 2010 02:01, Louis-Pierre Dahito
<lists-fsXkhYbjdPsEEoCn2XhGlw@public.gmane.org>
wrote:
> 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.
>
> 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:
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.

-- 
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk+unsubscribe-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en.

>   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!

LP

-- 
Posted via http://www.ruby-forum.com/.

-- 
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk+unsubscribe-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en.