[Dbix-class] Q: prefetch has_many relation

[Byron Young]

> -----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


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