[Dbix-class] Q: prefetch has_many relation

[Byron Young]

> -----Original Message-----
> From: Byron Young [mailto:Byron.Young at riverbed.com]
> Sent: Thursday, April 08, 2010 2:44 PM
> To: Class user and developer list
> Subject: RE: [Dbix-class] Q: prefetch has_many relation
> 
> > -----Original Message-----
> > From: Peter Rabbitson [mailto:rabbit+dbic at rabbit.us]
> > Sent: Thursday, April 08, 2010 9:16 AM
> > To: Class user and developer list
> > Subject: Re: [Dbix-class] Q: prefetch has_many relation
> >
> > Bernhard Graf wrote:
> > > Artist has_many CDs.
> > >
> [snip]
> > > How do I select all CDs of the artists of $artist_rs with one
> query?
> >
> > my $new_rs = $artist_rs->search ({}, { prefetch => 'cds' });
> >
> 
> This brings up something I've been wondering about lately, which is how
> prefetch and join cause the resultset to behave when you prefetch or
> join on one or more has_many relationships.
> 
> When I use prefetch on multiple has_many relationships, DBIC prints
> this warning:
> 
> "DBIx::Class::ResultSet::all(): Prefetching multiple has_many rels jobs
> and queues at top level will explode the number of row objects
> retrievable via ->next or ->all. Use at your own risk."
> 
> But I notice that $rs->all() actually returns the same number of rows
> as it does if I don't specify any prefetch or join (or if I specify a
> group_by => 'me.id').  If I specify a join instead of a prefetch it
> does actually explode the rows.  Here's output of a script I wrote to
> test this:
> 
> 
> DBIx::Class::ResultSet::all(): Prefetching multiple has_many rels jobs
> and queues at top level will explode the number of row objects
> retrievable via ->next or ->all. Use at your own risk. at get-column.pl
> line 26
> SELECT `me`.`id`, `me`.`name`, `me`.`modified_ts`, `jobs`.`id`,
> `jobs`.`task`, `jobs`.`active`, `jobs`.`scheduling_user`,
> `jobs`.`execution_host`, `jobs`.`log_dir`, `jobs`.`state`,
> `jobs`.`execution_trigger`, `jobs`.`start_time`, `jobs`.`end_time`,
> `jobs`.`last_state_change_time`, `jobs`.`modified_ts`,
> `jobs`.`email_recipients`, `jobs`.`use_user_resources`, `jobs`.`vlab`,
> `jobs`.`priority`, `jobs`.`is_debug`,
> `jobs`.`clean_up_failed_allocation`, `jobs`.`allocation`,
> `jobs`.`owner`, `jobs`.`release`, `jobs`.`pid`, `job_installs`.`id`,
> `job_installs`.`job`, `job_installs`.`install_instruction`,
> `job_installs`.`build_data`, `job_installs`.`trigger_tag`,
> `job_installs`.`trigger_type`, `queues`.`id`, `queues`.`vlab`,
> `queues`.`owner`, `queues`.`allocation`, `queues`.`created_ts`,
> `queues`.`modified_ts` FROM `vlabs` `me` LEFT JOIN `jobs` `jobs` ON
> `jobs`.`vlab` = `me`.`id` LEFT JOIN `job_installs` `job_installs` ON
> `job_installs`.`job` = `jobs`.`id` LEFT JOIN `queues` `queues` ON
> `queues`.`vlab` = `me`.`id` ORDER BY `jobs`.`vlab`,
> `job_installs`.`job`, `queues`.`vlab`:
> SELECT `me`.`id`, `me`.`name`, `me`.`modified_ts` FROM `vlabs` `me`
> LEFT JOIN `jobs` `jobs` ON `jobs`.`vlab` = `me`.`id` LEFT JOIN
> `job_installs` `job_installs` ON `job_installs`.`job` = `jobs`.`id`
> LEFT JOIN `queues` `queues` ON `queues`.`vlab` = `me`.`id`:
> SELECT `me`.`id`, `me`.`name`, `me`.`modified_ts` FROM `vlabs` `me`:
> Num with prefetch: 4
> Num with join: 146470
> Num with neither: 4
> 

I should have said that the first SELECT is the search with the prefetch, the second is the search with join, the third is the search with neither prefetch nor join.


Byron

> 
> The prefetch or join is this:
> 
>     [
>         { 'jobs' => 'job_installs' },
>         'queues'
>     ]
> 
> 'jobs', 'job_installs', and 'queues' are all has_many.
> 
> So what I'm wondering is, why the warning message?  If I'm prefetching
> across multiple levels of has_many relationships like this, can I rely
> on each resultset having the correct number of rows for the
> relationship (e.g. the same number as I would get if I don’t prefetch)?
> 
> Thanks,
> Byron