[Dbix-class] prefetch across multiple tables

Dagfinn Ilmari Mannsåker ilmari at ilmari.org
Wed Dec 17 08:37:26 GMT 2014

"John Stoffel" <john at stoffel.org> writes:

> Hi,

Hi John,

> I've been pounding my head trying to figure out what I'm doing wrong
> here.  I've got a simple three table DB:
> Names: full_name, name_id   
> has_many:
>   Account: account_id, boxfolder_id, URL, comment
>     has_one:
>       Boxfolder:  boxfolder_id, volume, folder, range
> Now when I try to use DBIx::Class (inside a Dancer web app) I just
> can't make it work.  My code looks like this:
> my $rs = $schema->resultset('Name')->search({ full_name => { like => $name }, },
>                                             { limit => 10,
>                                               order_by => { -asc => 'full_name' },
>                                               prefetch => [ 'account', { 'account' => 'boxfolder'}],

This is not what you want, as that'll join twice to the account table,
and then from one of those instances to the boxfolder table.

>                                             });
> And in the various schemas, generated with dbicdump, I have stuff like this:
> Name.pm:
> __PACKAGE__->has_many('account', 'Carey::Schema::Result::Account','name_id');
> Account.pm:
> __PACKAGE__->belongs_to('name', 'Carey::Schema::Result::Name','account_id');
> __PACKAGE__->has_one('boxfolder', 'Carey::Schema::Result::Boxfolder','boxfolder_id' );
> Boxfolder.pm:
> __PACKAGE__->belongs_to('account','Carey::Schema::Result::Account','boxfolder_id');

> I get stuff like this:
>> ../bin/dbic-test.pl Emlen
> DBIx::Class::ResultSet::next(): Prefetching multiple has_many rels account and account at top level will explode the number of row objects retrievable via ->next or ->all. Use at your own risk. at ../bin/dbic-test.pl line 19

This warning stems from the fact that you're joining twice to the
account table.  However, this warning does not exist in the current DBIC
version. Which version are you running?

> Full Name: Emlen & Howell (8213)
> Account: 5920  (boxfolder_id: 234)   URL: b16f10
> Can't call method "volume" on an undefined value at ../bin/dbic-test.pl line 28.

This error stems from the fact that it's the second instance of account
that has prefetched the boxfolder, not the first.

> I've tried multiple variations of the prefetch, like:
>    prefetch => { 'account' => 'boxfolder' }

This is the correct one and should work. Can you run this with the
DBI_TRACE environment variable set to 1, and show us the full output?

"The surreality of the universe tends towards a maximum" -- Skud's Law
"Never formulate a law or axiom that you're not prepared to live with
 the consequences of."                              -- Skud's Meta-Law

More information about the DBIx-Class mailing list