DBIx::Class prefetch problem

l8gravely has asked for the wisdom of the Perl Monks concerning the following question:

Hi,

I'm running into a problem with a Dancer web app using the Dancer::Plugin::DBIC, but I can replicate the issue when using just plain DBIx::Class for a standalone search. I'm trying to search in the Names table against the full_name field, then printing out all the data for each returned name in the Account and Boxfolder tables. The tables look like this:

mysql> describe names;
+------------+--------------+------+-----+---------+----------------+
| Field      | Type         | Null | Key | Default | Extra          |
+------------+--------------+------+-----+---------+----------------+
| name_id    | int(7)       | NO   | PRI | NULL    | auto_increment |
| full_name  | varchar(50)  | NO   |     | NULL    |                |
| last_name  | text         | YES  |     | NULL    |                |
| first_name | text         | YES  |     | NULL    |                |
| comments   | varchar(100) | YES  |     | NULL    |                |
+------------+--------------+------+-----+---------+----------------+
[download]

mysql> describe account;
+----------------+---------------+------+-----+---------+-------------
+---+
| Field          | Type          | Null | Key | Default | Extra       
+   |
+----------------+---------------+------+-----+---------+-------------
+---+
| account_id     | int(7)        | NO   | PRI | NULL    | auto_increme
+nt |
| account_number | varchar(10)   | NO   |     | NULL    |             
+   |
| boxfolder_id   | int(7)        | NO   |     | NULL    |             
+   |
| name_id        | int(7)        | YES  |     | NULL    |             
+   |
| url            | varchar(1028) | YES  |     | NULL    |             
+   |
| comments       | varchar(100)  | YES  |     | NULL    |             
+   |
+----------------+---------------+------+-----+---------+-------------
+---+
[download]

mysql> describe boxfolder;
+--------------+--------------+------+-----+---------+----------------
++
| Field        | Type         | Null | Key | Default | Extra          
+|
+--------------+--------------+------+-----+---------+----------------
++
| boxfolder_id | int(7)       | NO   | PRI | NULL    | auto_increment 
+|
| volume       | varchar(20)  | NO   |     | NULL    |                
+|
| box          | varchar(10)  | NO   |     | NULL    |                
+|
| folder       | varchar(20)  | YES  |     | NULL    |                
+|
| range        | text         | YES  |     | NULL    |                
+|
| comments     | varchar(100) | YES  |     | NULL    |                
+|
+--------------+--------------+------+-----+---------+----------------
++
[download]

I generated the following schema using the dbicdump command to automatically pull the data out. As you can see, the names table has a name_id which joins with the account table. The account table then has a join with the boxfolder table for each account_id entry, since there are alot of duplicate boxfolder_id entries in various account_id records. I guess I could just do a merge and de-normalize the boxfolder and account tables into one single table with duplicates if need be.

The mysql query I have that does the right thing is as follows:

mysql> SELECT n.name_id,n.full_name,a.account_id,b.boxfolder_id,b.volu
+me,b.folder,b.range,substring(a.url,118,locate('&',a.url,118)-118) AS
+ value2  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 REGEXP '[[:<:]]byrne[[:>:]]' LIMIT 10;
+---------+----------------------------+------------+--------------+--
+------+--------+-----------+---------+
| name_id | full_name                  | account_id | boxfolder_id | v
+olume | folder | range     | value2  |
+---------+----------------------------+------------+--------------+--
+------+--------+-----------+---------+
|    7220 | Byrne, E. & McCausland A.  |       2642 |          322 | 3
+1     | 8      | 5123-5148 | =b22f08 |
|    7221 | Byrne, G.                  |       2643 |          295 | 2
+9     | 11     | 4293-4314 | =b20f11 |
|    7222 | Byrne, John                |       2644 |          106 | 1
+6     | 1      | 6003-6128 | =b08f01 |
|    7223 | Byrne, P.                  |       2645 |          425 | 3
+7     | fol03  |           | =fol03  |
|    7224 | Byrne, P., Estate of       |       2646 |          425 | 3
+7     | fol03  |           | =fol03  |
|    7225 | Byrne, Patrick             |       2672 |          210 | 2
+3     | 1      | 1404-1433 | =b15f01 |
|    7225 | Byrne, Patrick             |       2673 |          210 | 2
+3     | 1      | 1404-1433 | =b15f01 |
|    7225 | Byrne, Patrick             |       2674 |          214 | 2
+3     | 5      | 1502-1538 | =b15f05 |
|    7225 | Byrne, Patrick             |       2675 |          215 | 2
+3     | 6      | 1539-1596 | =b15f06 |
|    7225 | Byrne, Patrick             |       2676 |          215 | 2
+3     | 6      | 1539-1596 | =b15f06 |
+---------+----------------------------+------------+--------------+--
+------+--------+-----------+---------+
[download]

but unfortunately, my DBIx::Class code uses the following query to return the wrong results:

mysql> SELECT me.name_id, me.full_name, me.last_name, me.first_name, m
+e.comments, account.account_id, account.account_number, account.boxfo
+lder_id, account.name_id, substring(account.url,118,locate('&',accoun
+t.url,118)-118) AS value2, account.comments, boxfolder.boxfolder_id, 
+boxfolder.volume, boxfolder.box, boxfolder.folder, boxfolder.range, b
+oxfolder.comments FROM (SELECT me.name_id, me.full_name, me.last_name
+, me.first_name, me.comments FROM names me WHERE ( full_name REGEXP '
+[[:<:]]Byrne[[:>:]]') ORDER BY full_name ASC LIMIT 5) me LEFT JOIN ac
+count account ON account.name_id = me.name_id LEFT JOIN boxfolder box
+folder ON boxfolder.boxfolder_id = account.account_id WHERE ( full_na
+me REGEXP '[[:<:]]Byrne[[:>:]]' ) ORDER BY full_name ASC;
+---------+----------------------------+-----------+------------+-----
+-----+------------+----------------+--------------+---------+--------
+-+----------+--------------+--------+------+--------+-------+--------
+--+
| name_id | full_name                  | last_name | first_name | comm
+ents | account_id | account_number | boxfolder_id | name_id | value2 
+ | comments | boxfolder_id | volume | box  | folder | range | comment
+s |
+---------+----------------------------+-----------+------------+-----
+-----+------------+----------------+--------------+---------+--------
+-+----------+--------------+--------+------+--------+-------+--------
+--+
|    7220 | Byrne, E. & McCausland A.  | NULL      | NULL       | NULL
+     |       2642 | 5139           |          322 |    7220 | =b22f08
+ | NULL     |         NULL | NULL   | NULL | NULL   | NULL  | NULL   
+  |
|    7221 | Byrne, G.                  | NULL      | NULL       | NULL
+     |       2643 | 4299           |          295 |    7221 | =b20f11
+ | NULL     |         NULL | NULL   | NULL | NULL   | NULL  | NULL   
+  |
|    7222 | Byrne, John                | NULL      | NULL       | NULL
+     |       2644 | 6052           |          106 |    7222 | =b08f01
+ | NULL     |         NULL | NULL   | NULL | NULL   | NULL  | NULL   
+  |
|    7223 | Byrne, P.                  | NULL      | NULL       | NULL
+     |       2645 | 396            |          425 |    7223 | =fol03 
+ | NULL     |         NULL | NULL   | NULL | NULL   | NULL  | NULL   
+  |
|    7224 | Byrne, P., Estate of       | NULL      | NULL       | NULL
+     |       2646 | 396            |          425 |    7224 | =fol03 
+ | NULL     |         NULL | NULL   | NULL | NULL   | NULL  | NULL   
+  |
+---------+----------------------------+-----------+------------+-----
+-----+------------+----------------+--------------+---------+--------
+-+----------+--------------+--------+------+--------+-------+--------
+--+
5 rows in set (0.20 sec)
[download]

and I'm going crazy trying to figure out why I'm getting these results. I admit I put in the substring part on the account.url field, otherwise the output gets crazy stupid long. In any case, here's my Schema, followed by my test code:

package Carey::Schema::Result::Name;
__PACKAGE__->table("names");
__PACKAGE__->add_columns(
  "name_id",
  { data_type => "integer", is_auto_increment => 1, is_nullable => 0 }
+,
  "full_name",
  { data_type => "varchar", is_nullable => 0, size => 50 },
  "last_name",
  { data_type => "text", is_nullable => 1 },
  "first_name",
  { data_type => "text", is_nullable => 1 },
  "comments",
  { data_type => "varchar", is_nullable => 1, size => 100 },
);
__PACKAGE__->set_primary_key("name_id");
__PACKAGE__->has_many('account','Carey::Schema::Result::Account','name
+_id');
[download]

package Carey::Schema::Result::Account;
__PACKAGE__->table("account");
__PACKAGE__->add_columns(
  "account_id",
  { data_type => "integer", is_auto_increment => 1, is_nullable => 0 }
+,
  "account_number",
  { data_type => "varchar", is_nullable => 0, size => 10 },
  "boxfolder_id",
  { data_type => "integer", is_nullable => 0 },
  "name_id",
  { data_type => "integer", is_nullable => 1 },
  "url",
  { data_type => "varchar", is_nullable => 1, size => 1028 },
  "comments",
  { data_type => "varchar", is_nullable => 1, size => 100 },
);
__PACKAGE__->set_primary_key("account_id");
__PACKAGE__->belongs_to('name', 'Carey::Schema::Result::Name','account
+_id');
__PACKAGE__->has_one('boxfolder', 'Carey::Schema::Result::Boxfolder','
+boxfolder_id' );
[download]

package Carey::Schema::Result::Boxfolder;
__PACKAGE__->table("boxfolder");
__PACKAGE__->add_columns(
  "boxfolder_id",
  { data_type => "integer", is_auto_increment => 1, is_nullable => 0 }
+,
  "volume",
  { data_type => "varchar", is_nullable => 0, size => 20 },
  "box",
  { data_type => "varchar", is_nullable => 0, size => 10 },
  "folder",
  { data_type => "varchar", is_nullable => 1, size => 20 },
  "range",
  { data_type => "text", is_nullable => 1 },
  "comments",
  { data_type => "varchar", is_nullable => 1, size => 100 },
);
__PACKAGE__->set_primary_key("boxfolder_id");
__PACKAGE__->belongs_to('account','Carey::Schema::Result::Account','bo
+xfolder_id');
[download]

And now the test code I'm using which generates the bogus mysql query which gets me the wrong data. I've tried removing the prefetch line, but no go. I'm obviously missing something here which I don't understand properly.

#!/usr/bin/perl -w

use DBIx::Class;
use lib '../lib';
use Carey::Schema;
use Data::Dumper;

die "Usage: $0 name\n\n" if $#ARGV < 0;
my $name = shift @ARGV;

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

#my $rs = $schema->resultset('Name')->search({ full_name => { like => 
+$name }, },
#                                           { rows => 5,
#                                             order_by => { -asc => 'f
+ull_name' },
#                                             prefetch => { 'account' 
+=> 'boxfolder'},
#                                           });

my $rs = $schema->resultset('Name')->search({ full_name =>
                                              { regexp => '[[:<:]]'.$n
+ame.'[[:>:]]' }
                                            },
                                            {
                                             rows => 5,
                                             order_by => { -asc => 'fu
+ll_name' },
                                             #join =>  ['account', { '
+account' => 'boxfolder' } ],
                                             prefetch => [ 'account', 
+{ 'account' => 'boxfolder' } \
],
                                            });
$schema->storage->debug(1);

#while (my $r = $rs->next) {
my @r = $rs->all;
foreach my $r (@r) {
  print "Full Name: ", $r->full_name, " (", $r->name_id, ")\n";
  
  foreach my $a ($r->account()) {
    print "  account_id=", $a->account_id();
    print " boxfolder_id=",$a->boxfolder_id()," ";
    my $t = $a->url();
    $t =~ m/value2=(\w+)\&/;
    print " URL: $1";
    $vol = $a->boxfolder->volume;
    $folder = $a->boxfolder->volume;
    $range = $a->boxfolder->range;
    print " V=$vol " if defined $vol;
    print " F=$folder " if defined $folder;
    print " R=$range " if defined $range;

    print "\n";
  }
}
[download]

Comment on DBIx::Class prefetch problem Select or Download Code

Replies are listed 'Best First'.
Re: DBIx::Class prefetch problem by Anonymous Monk on Jan 14, 2015 at 22:33 UTC
The tables look like this: That doesn't look like a schema :)	[reply]
Re^2: DBIx::Class prefetch problem by l8gravely (Initiate) on Jan 15, 2015 at 03:47 UTC
You're right, my question probably wasn't formatted properly. But with a bit of work, I think I've semi-managed to find a work around that I think will work. My new test script does the following, which is basically to run a single query to get the Names and Account tables joined together, then it runs a query for each boxfolder_id found in each Account_id for each Name_id, which is crappily inefficient, but which does the trick in my test script. Now to figure out why it's not working in my Dancer web app the way I expect. My new code is: #!/usr/bin/perl -w use DBIx::Class; use lib '../lib'; use Carey::Schema; use Data::Dumper; die "Usage: $0 name\n\n" if $#ARGV < 0; my $name = shift @ARGV; my $schema = Carey::Schema->connect('DBI:mysql:database=careymss;host= +localhost;port=3306', 'kiddb','', { PrintError => 1, RaiseError => 1} +); my $rs = $schema->resultset('Name')->search({ full_name => { regexp => '[[:<:]]'.$n +ame.'[[:>:]]' } }, { rows => 10, prefetch => 'account', order_by => { -asc => 'fu +ll_name' }, }); $schema->storage->debug(1); my @r = $rs->all; foreach my $r (@r) { print "Full Name: ", $r->full_name, " (", $r->name_id, ")\n"; foreach my $a ($r->account()) { print " account_id=", $a->account_id(); print " boxfolder_id=",$a->boxfolder_id()," "; my $t = $a->url(); $t =~ m/value2=(\w+)\&/; print " URL: $1"; my $b = $a->boxfolder(); $vol = $b->{volume}; $folder = $b->{folder}; $range = $b->{range}; print " V=$vol " if defined $vol; print " F=$folder " if defined $folder; print " R=$range " if defined $range; print "\n"; } } [download] And I'd appreciate any comnments. Thanks, John	[reply] [d/l]
Re^3: DBIx::Class prefetch problem by Anonymous Monk on Jan 15, 2015 at 03:57 UTC
..And I'd appreciate any comnments. Thanks, John Ok, the bar is low enough :) so here is any comments You've posted a description of your tables, which looks very much like an actual schema, but it isn't an actual schema, so anyone actually trying to run that code to help, needs to recreate the schema ... manual labor you've already done that nobody needs to repeat or already have tons of experience with DBIx and can just figure out the issue you're having just by looking (not me, i'd have to sqlt the schema to sqlite and go from there) Chances are you can find some such person at the irc channel, link https://chat.mibbit.com/#dbix-class@irc.perl.org	[reply]
Re^4: DBIx::Class prefetch problem by l8gravely (Initiate) on Jan 18, 2015 at 22:57 UTC