[Dbix-class] Problem prefetching more than one relation

[Jon Warbrick]

On Wed, 8 Nov 2006, Matt S Trout wrote:

> Jon Warbrick wrote:
> > Am I limited to pre-fetching only one has_many relation?
> 
> >   $db->resultset("Person")->search
> >       ({ "me.uid" => 'fjc55' },
> >        { prefetch => [qw/title mail/]})->single
> 
> At the same level, like that, for the moment yes.
> 
> A chain of has_manys is fine.
> 
> For the record, I've found it's usually not very efficient to do that anyway
> for large resultsets because of the cross product effect, and fairly
> irrelevant for small ones.

On reflection I'd concluded that multiple has_many prefetches, while more 
DWIM and perhaps slightly more efficient, were not vital. 

But now I've realised that not having them is causing problems. My Person 
objects have multiple titles and mail addresses. I'd planned to pass 
person objects to web page templates which render person details. I now 
realise that as things stand, every reference to titles or a mail 
addresses results in a hit on the database. Since I need to display titles 
and mail addresses more than once in some templates this causes a lot of 
unnecessary database traffic.

I realise that I could fetch the title and mail records themselves 
in my controller and pass them explicitly to the template (or perhaps pass 
ResultSets created with {cache=>1}). Or I could retrieve the records in 
the template and cache them so that I could use the same copies each time.

None of these seem ideal, not least because in practise there are more 
than 2 has_many relations in Person, and some of these are to other 
objects with their own has_many relationships to other objects. Am I 
missing some 'obvious' way to do this? If not, can anyone suggest a better 
way to go about this?

Jon.

-- 
Jon Warbrick
Web/News Development, Computing Service, University of Cambridge