Eager load for 1:N across natural key join

rubyonrails-talk
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

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

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&gt;