# ActiveRecord-2.2.2, association preloading, joins and order parameter weirdness

**URL:** <https://www.ruby-forum.com/t/activerecord-2-2-2-association-preloading-joins-and-order-parameter-weirdness/160817>\
**Category:** Rails\
**Created:** [February 20, 2009, 12:04am UTC](https://www.ruby-forum.com/t/activerecord-2-2-2-association-preloading-joins-and-order-parameter-weirdness/160817 "2009-02-20T00:04:51Z")\
**Posts on this page:** 2\
**Page:** 1

<div class="post-metadata">

**Author:** ![michaelb](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/m/b19c9b/32.png) [@michaelb](https://www.ruby-forum.com/u/michaelb)\
**Post date:** [February 20, 2009, 12:04am UTC](https://www.ruby-forum.com/t/activerecord-2-2-2-association-preloading-joins-and-order-parameter-weirdness/160817/1 "2009-02-20T00:04:51Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![michaelb](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/m/b19c9b/32.png) [@michaelb](https://www.ruby-forum.com/u/michaelb)\
**Post date:** [February 20, 2009, 12:07am UTC](https://www.ruby-forum.com/t/activerecord-2-2-2-association-preloading-joins-and-order-parameter-weirdness/160817/2 "2009-02-20T00:07:46Z")

</div>

On Feb 19, 8:18 pm, “Michael B.” [removed\_email\_address@domain.invalid](mailto:removed_email_address@domain.invalid) 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).

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