# Eager Loading a Relationship That Has No PK/FK

**URL:** <https://www.ruby-forum.com/t/eager-loading-a-relationship-that-has-no-pk-fk/176962>\
**Category:** Rails\
**Created:** [October 9, 2009, 1:02am UTC](https://www.ruby-forum.com/t/eager-loading-a-relationship-that-has-no-pk-fk/176962 "2009-10-09T01:02:53Z")\
**Posts on this page:** 7\
**Page:** 1

<div class="post-metadata">

**Author:** ![MaggotChild](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/m/7ea924/32.png) [@MaggotChild](https://www.ruby-forum.com/u/MaggotChild)\
**Post date:** [October 9, 2009, 1:02am UTC](https://www.ruby-forum.com/t/eager-loading-a-relationship-that-has-no-pk-fk/176962/1 "2009-10-09T01:02:53Z")

</div>

I’m attempting to wrestle an old DB into Rails.  
This relationship is giving me trouble:

class Show \< AR::Base  
has\_many :segments  
end

class Segment \< AR::Base  
belongs\_to :show  
has\_one :media #this has no PK/FK relation  
end

A Segment is “linked” to Media by Media.name, which is the result of  
concatenating Segment.name and Segment.part. As I said there are is no  
PK/PK relation so the actual Segment class looks like this:

class Segment \< AR::Base  
def media  
@media ||= Media.find\_by\_name("#{name}%02d" % part)  
end  
end

This leaves me stuck with a N+1 select problem because I can’t say:  
Segment.all :include =\> :media

or

Show.all :include =\> {:segment=\>:media}

How to get around the N+1? select problem and eager load Media?

In the case of Show.all, Show and Media have a relation (yes bad  
schema) so I was thinking I could pull all the  
Media after a the Segment collection is loaded by Show and assign them  
to the Segments accordingly.  
But, while there is an after\_find, this is applied after each object  
and not after the collection.

Any ideas?

Thanks

---

<div class="post-metadata">

**Author:** ![MaggotChild](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/m/7ea924/32.png) [@MaggotChild](https://www.ruby-forum.com/u/MaggotChild)\
**Post date:** [October 9, 2009, 1:26am UTC](https://www.ruby-forum.com/t/eager-loading-a-relationship-that-has-no-pk-fk/176962/2 "2009-10-09T01:26:54Z")

</div>

On Oct 9, 12:01 am, MaggotChild [removed\_email\_address@domain.invalid](mailto:removed_email_address@domain.invalid) wrote:

> This leaves me stuck with a N+1 select problem because I can’t say:  
> Segment.all :include =\> :media

> or
> 
> Show.all :include =\> {:segment=\>:media}

I think you’ll find it hard to get that exactly to work. It shouldn’t  
be hard however to do

shows = Show.all :include =\> :segment  
load\_media(shows.collect {|s| s.segment})

your load\_media function will need to iterate over the segments,  
construct all the names, load those segments and then assign to each  
segment what its media is

Fred

---

<div class="post-metadata">

**Author:** ![MaggotChild](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/m/7ea924/32.png) [@MaggotChild](https://www.ruby-forum.com/u/MaggotChild)\
**Post date:** [October 9, 2009, 7:21pm UTC](https://www.ruby-forum.com/t/eager-loading-a-relationship-that-has-no-pk-fk/176962/3 "2009-10-09T19:21:03Z")

</div>

BTW, if the “old DB” in question is going away after you complete the  
new one, I’d suggest that you’d be better off migrating the data to a  
more Railsish structure all at once (essentially an import process)  
and then forget about the hinky structure of the old DB. I’ve worked  
with several absolute disasters (thanks, PHP guys!) that ended up  
getting the data extracted via DBI and inserted as completely new  
records.

This obviously doesn’t apply if you’re stuck in a situation where the  
old DB will continue to be used…

–Matt J.

---

<div class="post-metadata">

**Author:** ![MaggotChild](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/m/7ea924/32.png) [@MaggotChild](https://www.ruby-forum.com/u/MaggotChild)\
**Post date:** [October 9, 2009, 7:57pm UTC](https://www.ruby-forum.com/t/eager-loading-a-relationship-that-has-no-pk-fk/176962/4 "2009-10-09T19:57:56Z")

</div>

On Oct 8, 4:25 pm, Frederick C. [removed\_email\_address@domain.invalid](mailto:removed_email_address@domain.invalid)  
wrote:

> construct all the names, load those segments and then assign to each  
> segment what its media is

Hi Fred,

The problem with that is the location of load\_media(). Load media  
needs to be inside of Show, as it’s responsible for loading a Show  
and it’s Segments. Anyone client using a Show finder would now need  
the load\_media() function. Plus load\_media() is really doing what AR’s  
association\_preload is already doing.

I figured out that there is actually an easy solution for this (too  
bad I’m using the CPK module which fails to support it -omitted for  
brevity):

class Segment \< AR::Base  
belongs\_to :show  
has\_one :media, :primary\_key =\> :media\_name

def media\_name  
“#{name}%02d” % part  
end  
end

Thanks!

---

<div class="post-metadata">

**Author:** ![MaggotChild](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/m/7ea924/32.png) [@MaggotChild](https://www.ruby-forum.com/u/MaggotChild)\
**Post date:** [October 9, 2009, 7:24pm UTC](https://www.ruby-forum.com/t/eager-loading-a-relationship-that-has-no-pk-fk/176962/5 "2009-10-09T19:24:50Z")

</div>

MaggotChild wrote:

> I’m attempting to wrestle an old DB into Rails.  
> This relationship is giving me trouble:
> 
> class Show \< AR::Base  
> has\_many :segments  
> end
> 
> class Segment \< AR::Base  
> belongs\_to :show  
> has\_one :media #this has no PK/FK relation  
> end
> 
> A Segment is “linked” to Media by Media.name, which is the result of  
> concatenating Segment.name and Segment.part. As I said there are is no  
> PK/PK relation so the actual Segment class looks like this:
> 
> class Segment \< AR::Base  
> def media  
> @media ||= Media.find\_by\_name(“#{name}%02d” % part)  
> end  
> end
> 
> This leaves me stuck with a N+1 select problem because I can’t say:  
> Segment.all :include =\> :media

The easiest thing to do would be to modify the media table so as to  
declare the name to be primary key. Rails _will_ work with string keys,  
and anyway you should generally not have a keyless table.

## Best,

Marnen Laibow-Koser  
[http://www.marnen.org](http://www.marnen.org)  
removed\_email\_address@domain.invalid

---

<div class="post-metadata">

**Author:** ![MaggotChild](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/m/7ea924/32.png) [@MaggotChild](https://www.ruby-forum.com/u/MaggotChild)\
**Post date:** [October 9, 2009, 9:07pm UTC](https://www.ruby-forum.com/t/eager-loading-a-relationship-that-has-no-pk-fk/176962/6 "2009-10-09T21:07:29Z")

</div>

MaggotChild wrote:

> Modifying the DB isn’t possible but …

Okay. Make a new database, import the data into it (don’t forget to add  
the railsy primary keys), and MAGIC.

---

<div class="post-metadata">

**Author:** ![MaggotChild](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/m/7ea924/32.png) [@MaggotChild](https://www.ruby-forum.com/u/MaggotChild)\
**Post date:** [October 9, 2009, 8:00pm UTC](https://www.ruby-forum.com/t/eager-loading-a-relationship-that-has-no-pk-fk/176962/7 "2009-10-09T20:00:52Z")

</div>

On Oct 9, 10:24 am, Marnen Laibow-Koser \<rails-mailing-l…@andreas-  
[s.net](http://s.net)\> wrote:

> > has\_one :media #this has no PK/FK relation  
> > end
> 
> > This leaves me stuck with a N+1 select problem because I can’t say:  
> > Segment.all :include =\> :media
> 
> The easiest thing to do would be to modify the media table so as to  
> declare the name to be primary key.

Hi Maren,

Modifying the DB isn’t possible but I could use the :primary\_key  
options to has one:

class Segment \< AR::Base  
belongs\_to :show  
has\_one :media, :primary\_key =\> :media\_name

def media\_name  
“#{name}%02d” % part  
end  
end

Thanks!