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

**URL:** <https://www.ruby-forum.com/t/sql-error-joins-and-order-in-activerecord-find-command/187535>\
**Category:** Rails\
**Created:** [April 25, 2010, 3:01am UTC](https://www.ruby-forum.com/t/sql-error-joins-and-order-in-activerecord-find-command/187535 "2010-04-25T03:01:36Z")\
**Posts on this page:** 3\
**Page:** 1

<div class="post-metadata">

**Author:** ![lpdahito](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/l/d2c977/32.png) [@lpdahito](https://www.ruby-forum.com/u/lpdahito)\
**Post date:** [April 25, 2010, 3:01am UTC](https://www.ruby-forum.com/t/sql-error-joins-and-order-in-activerecord-find-command/187535/1 "2010-04-25T03:01:36Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![lpdahito](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/l/d2c977/32.png) [@lpdahito](https://www.ruby-forum.com/u/lpdahito)\
**Post date:** [April 25, 2010, 10:01am UTC](https://www.ruby-forum.com/t/sql-error-joins-and-order-in-activerecord-find-command/187535/2 "2010-04-25T10:01:57Z")

</div>

On 25 April 2010 02:01, Louis-Pierre D. [removed\_email\_address@domain.invalid](mailto:removed_email_address@domain.invalid)  
wrote:

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

---

<div class="post-metadata">

**Author:** ![lpdahito](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/l/d2c977/32.png) [@lpdahito](https://www.ruby-forum.com/u/lpdahito)\
**Post date:** [April 25, 2010, 9:28pm UTC](https://www.ruby-forum.com/t/sql-error-joins-and-order-in-activerecord-find-command/187535/3 "2010-04-25T21:28:43Z")

</div>

> 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