[Dbix-class] prefetch across multiple tables

[John Stoffel]

Hi,

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

I can do simple SQL queries, looking for full_name like this:

mysql> SELECT n.name_id,n.full_name,a.account_id,b.boxfolder_id,b.volume,b.folder,b.range FROM names as n LEFT JOIN account AS a ON n.name_id = a.name_id LEFT JOIN boxfolder AS b on a.boxfolder_id = b.boxfolder_id WHERE n.full_name LIKE 'Emlen%';
+---------+----------------+------------+--------------+--------+--------+-----------+
| name_id | full_name      | account_id | boxfolder_id | volume | folder | range     |
+---------+----------------+------------+--------------+--------+--------+-----------+
|    8213 | Emlen & Howell |       5920 |          234 | 24     | 10     | 2237-2266 |
|    8213 | Emlen & Howell |       5921 |          249 | 26     | 10     | 2792-2823 |
|    8213 | Emlen & Howell |       5922 |          281 | 28     | 12     | 4022-4063 |
|    8214 | Emlen, Caleb   |       5923 |           14 | 2      | 14     | 322-347   |
|    8214 | Emlen, Caleb   |       5924 |           50 | 6      | 5      | 2035-2082 |
+---------+----------------+------------+--------------+--------+--------+-----------+
5 rows in set (0.00 sec)


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:

#!/usr/bin/perl -w
use DBIx::Class;
use lib '../lib';
use Carey::Schema;
use Data::Dumper;

my $name = shift @ARGV;
$name .= "%";

my $schema = Carey::Schema->connect('DBI:mysql:database=foo;host=localhost;port=3306', 'password','', { PrintError => 1, RaiseError => 1});

my $rs = $schema->resultset('Name')->search({ full_name => { like => $name }, },
                                            { limit => 10,
                                              order_by => { -asc => 'full_name' },
                                              prefetch => [ 'account', { 'account' => 'boxfolder'}],
                                            });

while (my $r = $rs->next) {
  print "Full Name: ", $r->full_name, " (", $r->name_id, ")\n";

  foreach my $a ($r->account()) {
    print "Account: ", $a->account_id();
    print "  (boxfolder_id: ", $a->get_column('boxfolder_id'), ") ";
    my $t = $a->url();
    $t =~ m/value2=(\w+)\&/;
    print "  URL: $1\n";
    $vol = $a->boxfolder()->volume;
    print "VOL: $vol\n";
    print "\n";
  }
}


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');



Now when I run the above code, looking for a name like "Emlen" (don't ask!  It's not my data... :-)

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


I've tried multiple variations of the prefetch, like:

   prefetch => { 'account' => 'boxfolder' }
   prefetch => [ 'account', 'boxfolder' }

but I'm missing something, even though I've been reading and
re-reading the DBIx::Class::Manual::Cookbook back and forth and trying
to wrap my brain around what I'm missing.

What am I missing?

Thanks,
John