Rails - Feb 2009 - ActiveRecord-2.2.2, association preloading, joins and order parameter weirdness

Hello RoR Groupies,

I''m seeing some weird ActiveRecord-2.2.2 SQL generation when doing
eager association/preloading and using joins and ordering by said
join table.

Here''s an example:

class Photo < ActiveRecord::Base
end

class User < ActiveRecord::Base
  has_one :headshot, :class_name => "Photo"
end

class Mail < ActiveRecord::Base
  belongs_to :user
end


Mail.find(:all,
   :joins => "INNER JOIN users u ON u.id = mails.user_id",
  :conditions => "mails.user_id=1",
  :include => { :user => :headshot },
  :order => "u.name")

Now, the following SQL will be generated for the above statement:

SELECT `mails`.`id` AS t0_r0, `mails` ...  `users`.`id` AS t1_r0, ..
`photos`.`id` AS t2_r0,  FROM `mails` LEFT OUT JOIN `users` ON
`users`.id = `mails`.user_id LEFT OUTER JOIN `photos` ON
photos.user_id = users.id INNER JOIN users u ON u.id = mails.user_id
WHERE (mails.user_id=1) ORDER BY u.name

Notice the LEFT OUTER join being added. While the SQL is okay and gets
the right results, its less than efficient.

HOWEVER, if the order parameter is changed to NOT include u.name (say
use mails.created_at instead as an example), or removed completely,
ActiveRecord will generate 3 SQL statements.

SELECT * FROM `mail` NNER JOIN users u ON u.id = mails.user_id WHERE
(mails.user_id=1) ORDER BY u.name
SELECT * FROM `users` WHERE ...
SELECT * FROM `photos` WHERE ...

The two questions I have are:

1) Why AR is  paying attention to what''s in the ORDER parameter and
choosing the eager loading strategy based from that?

2) Is there any way I can force AR to choose the loading strategy?  Or
at least tell it not to do a LEFT  OUTER JOIN.

-- Michael

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

On Feb 19, 8:18 pm, "Michael B."
<lionsb...-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org>
wrote:
> The two questions I have are:
>
> 1) Why AR is  paying attention to what''s in the ORDER parameter
and
> choosing the eager loading strategy based from that?
Mainly for backwards compatibility AR falls back to the join based
include stuff when it thinks you''re referencing (in conditions, order
etc..) something from one of the included tables, because clearly
having a condition on a table you don''t join won''t work. In
2.2 it''s
not smart enough to scan the joins clause to see that references to
those tables are OK (it is in rails 2.3).
> 2) Is there any way I can force AR to choose the loading strategy?  Or
> at least tell it not to do a LEFT  OUTER JOIN.
Not that I know of

Fred
>
> -- Michael
--~--~---------~--~----~------------~-------~--~----~
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@googlegroups.com
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en
-~----------~----~----~----~------~----~------~--~---