James Kebinger
2007-Mar-31 18:37 UTC
Active record always appending "order by created_at desc"
Hello all, I''m having a problem with activerecord and wondering if
anyone else has encountered this. (using rails 1.2.3)
I''m doing a find all and adding my own :order option, but whatever I
add, AR still adds another "created_at desc" to the end of the order
list. This is causing the query to blow up when created_at is ambiguous.
This is the query:
@user.notes.find(:all, :include =>
[:project],:order=>"notes.created_at").each do |note|
and the sql ends up like this:
ActiveRecord::StatementInvalid (Mysql::Error: Column
''created_at'' in
order clause is ambiguous: SELECT notes.`id` AS t0_r0, notes.`user_id`
AS t0_r1, notes.`project_id` AS t0_r2, notes.`body` AS t0_r3,
notes.`created_at` AS t0_r4, notes.`updated_at` AS t0_r5, projects.`id`
AS t1_r0, projects.`name` AS t1_r1, projects.`position` AS t1_r2,
projects.`user_id` AS t1_r3, projects.`description` AS t1_r4,
projects.`state` AS t1_r5, projects.`created_at` AS t1_r6,
projects.`updated_at` AS t1_r7, projects.`default_context_id` AS t1_r8
FROM notes LEFT OUTER JOIN projects ON projects.id = notes.project_id
WHERE (notes.user_id = 1) ORDER BY notes.created_at, created_at DESC)
poking around Active record, I found this code from base.rb, which
appends an order by from the scope, but in the case above where is the
scope coming from?
def add_order!(sql, order, scope = :auto)
scope = scope(:find) if :auto == scope
scoped_order = scope[:order] if scope
if order
sql << " ORDER BY #{order}"
sql << ", #{scoped_order}" if scoped_order
else
sql << " ORDER BY #{scoped_order}" if scoped_order
end
end
--
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-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en
-~----------~----~----~----~------~----~------~--~---
Mark Reginald James
2007-Apr-01 20:39 UTC
Re: Active record always appending "order by created_at desc"
James Kebinger wrote:> I''m doing a find all and adding my own :order option, but whatever I > add, AR still adds another "created_at desc" to the end of the order > list. This is causing the query to blow up when created_at is ambiguous. > > This is the query: > @user.notes.find(:all, :include => > [:project],:order=>"notes.created_at").each do |note| > > and the sql ends up like this: > ... > WHERE (notes.user_id = 1) ORDER BY notes.created_at, created_at DESC)Does the declaration of the notes association in the Order model have a :order option? -- We develop, watch us RoR, in numbers too big to ignore. --~--~---------~--~----~------------~-------~--~----~ 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-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org To unsubscribe from this group, send email to rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en -~----------~----~----~----~------~----~------~--~---
Thanks for replying. I believe it does. Is there a way to deactivate or override that ordering for the purpose of this query? Mark Reginald James wrote:> James Kebinger wrote: > >> WHERE (notes.user_id = 1) ORDER BY notes.created_at, created_at DESC) > Does the declaration of the notes association in the Order model > have a :order option? > > -- > We develop, watch us RoR, in numbers too big to ignore.-- 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-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org To unsubscribe from this group, send email to rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en -~----------~----~----~----~------~----~------~--~---
court3nay
2007-Apr-02 01:33 UTC
Re: Active record always appending "order by created_at desc"
On Mar 31, 11:37 am, James Kebinger <rails-mailing-l...-ARtvInVfO7ksV2N9l4h3zg@public.gmane.org> wrote:> Hello all, I''m having a problem with activerecord and wondering if > anyone else has encountered this. (using rails 1.2.3) > > I''m doing a find all and adding my own :order option, but whatever I > add, AR still adds another "created_at desc" to the end of the order > list. This is causing the query to blow up when created_at is ambiguous. > > This is the query: > @user.notes.find(:all, :include => > [:project],:order=>"notes.created_at").each do |note| >The error is coming from the :include because your association doesn''t define the table name. I believe you want your association to look like class User has_many :notes, :order => "notes.created_at desc" end As far as I know you can''t prevent that from scoping all your find calls; However, in your case the order is the same in the association as the find, so, who cares. Side note, this is harder to read but an alternative solution that will stay locked to your note table name if you change it or something later. class User has_many :notes, :order => "#{Note.table_name}.created_at desc" end =Courtenay http://blog.caboo.se --~--~---------~--~----~------------~-------~--~----~ 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-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org To unsubscribe from this group, send email to rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en -~----------~----~----~----~------~----~------~--~---
Mark Reginald James
2007-Apr-02 02:59 UTC
Re: Active record always appending "order by created_at desc
James wrote:> Thanks for replying. I believe it does. Is there a way to deactivate or > override that ordering for the purpose of this query?The most simple way would be to declare another identical but unordered association. But if you really want to have one association and to leave the default order option in place, you should be able to do use reflect_on_association to change the order option, then change it back. See: http://groups.google.com/group/rubyonrails-talk/msg/06a1f5da4203a11d -- We develop, watch us RoR, in numbers too big to ignore. --~--~---------~--~----~------------~-------~--~----~ 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-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org To unsubscribe from this group, send email to rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en -~----------~----~----~----~------~----~------~--~---