# Eager load for 1:N across natural key join

**URL:** <https://www.ruby-forum.com/t/eager-load-for-1-n-across-natural-key-join/90146>\
**Category:** Rails\
**Created:** [March 26, 2007, 12:47pm UTC](https://www.ruby-forum.com/t/eager-load-for-1-n-across-natural-key-join/90146 "2007-03-26T12:47:20Z")\
**Posts on this page:** 5\
**Page:** 1

<div class="post-metadata">

**Author:** ![matthew](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/m/ed655f/32.png) [@matthew](https://www.ruby-forum.com/u/matthew)\
**Post date:** [March 26, 2007, 12:47pm UTC](https://www.ruby-forum.com/t/eager-load-for-1-n-across-natural-key-join/90146/1 "2007-03-26T12:47:20Z")

</div>

Hi List

I have a natural key association between Release and Track (so no id  
foreign key relationship).

I figured out how to get ActiveRecord to correctly populate the nested  
collections by declaring a :finder\_sql on the Release side :

has\_many :tracks, :finder\_sql =\> ‘SELECT \* FROM tracks WHERE  
tracks.album = “#{title}”’

Trouble is, if I wanna use an eager loader in the controller,  
ActiveRecord reverts back to LEFT OUTER JOINS and tries to use ids ?

Release.find(:all, :include=\>[:track])

I’d much prefer to have Rails do the mapping correctly so I can say :

release.artist.name

which I don’t get if I resort to raw SQL in the finder (I think) ?

Anyone give me a few pointers ?

Cheers

Matt

---

<div class="post-metadata">

**Author:** ![matthew](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/m/ed655f/32.png) [@matthew](https://www.ruby-forum.com/u/matthew)\
**Post date:** [March 26, 2007, 2:37pm UTC](https://www.ruby-forum.com/t/eager-load-for-1-n-across-natural-key-join/90146/2 "2007-03-26T14:37:50Z")

</div>

[http://groups.google.com/group/rubyonrails-talk/browse\_thread/thread/9b51fcd727d171b0/b606a9468a274dc5](http://groups.google.com/group/rubyonrails-talk/browse_thread/thread/9b51fcd727d171b0/b606a9468a274dc5)

–  
We develop, watch us RoR, in numbers too big to ignore.

---

<div class="post-metadata">

**Author:** ![matthew](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/m/ed655f/32.png) [@matthew](https://www.ruby-forum.com/u/matthew)\
**Post date:** [March 27, 2007, 4:01am UTC](https://www.ruby-forum.com/t/eager-load-for-1-n-across-natural-key-join/90146/3 "2007-03-27T04:01:33Z")

</div>

Hi Mark

Thanks for the tip.

Still feeling my way through rails so you’ll have to bear with me.

First thing is ensuring I’ve correctly bound in the mod script. I  
placed the script under /lib and added “require ‘eager\_custom\_sql’” to  
environment.rb

I tried to ensure this code was getting executed by firing up script/  
breakpointer and placing breakpoints at the start of each method. The  
code doesn’t breakpoint ? The breakpointer works fine if I just place  
a breakpoint in a controller…

If I DO have this mod config correctly I am still unsure if it does  
what I want as rails still halts complaining about a missing ID  
column. Seemingly not running the :finder\_sql from the has\_many  
assoc ?

I couldn’t find much on the web about how to use this so perhaps I am  
missing something more fundamental ?

Do I have to rewrite the :finder\_sql to use the t0\_rm syntax or must I  
explicitly pass in extra SQL in my finder call in the controller ?

TIA

Matt

---

<div class="post-metadata">

**Author:** ![matthew](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/m/ed655f/32.png) [@matthew](https://www.ruby-forum.com/u/matthew)\
**Post date:** [March 27, 2007, 8:25am UTC](https://www.ruby-forum.com/t/eager-load-for-1-n-across-natural-key-join/90146/4 "2007-03-27T08:25:52Z")

</div>

Matt S. wrote:

> First thing is ensuring I’ve correctly bound in the mod script. I  
> placed the script under /lib and added “require ‘eager\_custom\_sql’” to  
> environment.rb

That’s correct.

> I tried to ensure this code was getting executed by firing up script/  
> breakpointer and placing breakpoints at the start of each method. The  
> code doesn’t breakpoint ? The breakpointer works fine if I just place  
> a breakpoint in a controller…
> 
> If I DO have this mod config correctly I am still unsure if it does  
> what I want as rails still halts complaining about a missing ID  
> column. Seemingly not running the :finder\_sql from the has\_many  
> assoc ?

Sorry, it seems some core code changes from Rails 1.1 to 1.2  
made this mod no longer work. I’ve done an untested 1.2 version here:

```
http://mrj.bpa.nu/eager_custom_sql_rails_1.2.rb

```

> Do I have to rewrite the :finder\_sql to use the t0\_rm syntax or must I  
> explicitly pass in extra SQL in my finder call in the controller ?

The former.

–  
We develop, watch us RoR, in numbers too big to ignore.

---

<div class="post-metadata">

**Author:** ![matthew](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/m/ed655f/32.png) [@matthew](https://www.ruby-forum.com/u/matthew)\
**Post date:** [March 30, 2007, 6:05am UTC](https://www.ruby-forum.com/t/eager-load-for-1-n-across-natural-key-join/90146/5 "2007-03-30T06:05:48Z")

</div>

I took the hit of n + 1 in the end as, in my case, n = 8 and isn’t  
growing quickly. I didn’t try the code below but if anyone else has  
similar problems I came across this today. Again, not used it but…

[http://kellogg-assoc.com/articles/2006/11/05/eager-finder-sql](http://kellogg-assoc.com/articles/2006/11/05/eager-finder-sql)