Eager Loading a Relationship That Has No PK/FK

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

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}

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

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 Jones

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!

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!

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.