Eager Loading a Relationship That Has No PK/FK

Rails
1

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

2

On Oct 9, 12:01 am, MaggotChild [email protected] 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

3

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.

4

On Oct 8, 4:25 pm, Frederick C. [email protected]
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!

5

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
[email protected]

6

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.

7

On Oct 9, 10:24 am, Marnen Laibow-Koser <rails-mailing-l…@andreas-
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!