Eager load for 1:N across natural key join

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

http://groups.google.com/group/rubyonrails-talk/browse_thread/thread/9b51fcd727d171b0/b606a9468a274dc5

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

Matt Spendlove 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.

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>