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:
-
Why AR is paying attention to what’s in the ORDER parameter and
choosing the eager loading strategy based from that? -
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