> -----Original Message-----
> From: Peter Rabbitson [mailto:[email protected]]
> 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
_______________________________________________
List: http://lists.scsys.co.uk/cgi-bin/mailman/listinfo/dbix-class
IRC: irc.perl.org#dbix-class
SVN: http://dev.catalyst.perl.org/repos/bast/DBIx-Class/
Searchable Archive: http://www.grokbase.com/group/[email protected]
