Eager load for 1:N across natural key join


#1

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


#2

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


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


#3

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


#4

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.


#5

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