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 | | +------------+--------------+------+-----+---------+----------------+
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 | + | +----------------+---------------+------+-----+---------+------------- +---+
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 | +| +--------------+--------------+------+-----+---------+---------------- ++
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 | +---------+----------------------------+------------+--------------+-- +------+--------+-----------+---------+
but unfortunately, my DBIx::Class code uses the following query to return the wrong results:
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: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)
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');
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' );
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');
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"; } }
In reply to DBIx::Class prefetch problem by l8gravely
| For: | Use: | ||
| & | & | ||
| < | < | ||
| > | > | ||
| [ | [ | ||
| ] | ] |