Rails - Oct 2009 - 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

On Oct 9, 12:01 am, MaggotChild
<hsomob1...-/E1597aS9LQAvxtiuMwx3w@public.gmane.org>
wrote:
> 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
> 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

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

On Oct 8, 7:01 pm, MaggotChild
<hsomob1...-/E1597aS9LQAvxtiuMwx3w@public.gmane.org>
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
>
> 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

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
marnen-sbuyVjPbboAdnm+yROfE0A@public.gmane.org
-- 
Posted via http://www.ruby-forum.com/.

On Oct 8, 4:25 pm, Frederick Cheung
<frederick.che...-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org>
wrote:
> On Oct 9, 12:01 am, MaggotChild
<hsomob1...-/E1597aS9LQAvxtiuMwx3w@public.gmane.org> wrote:
> > 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
>
> 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
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!

On Oct 9, 10:24 am, Marnen Laibow-Koser <rails-mailing-l...@andreas-
s.net> wrote:
> 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.  
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.
-- 
Posted via http://www.ruby-forum.com/.