>>>>> "Peter" == Peter Rabbitson <rabbit+d...@rabbit.us> writes:
Peter> On 01/13/2015 03:33 AM, John Stoffel wrote: >> # search in Carey >> my @r = $schema->resultset('Name')->search( { full_name => >> { regexp => '[[:<:]]'.$query.'[[:>:]]' } >> }, >> { >> order_by => { -asc => 'full_name' }, >> prefetch => { 'account' => 'boxfolder' }, >> rows => $limit, >> }); >> return @r; >> >> >> >> And I don't get back consistent the info I expect. Sometimes it works >> and I get what I think is the right info, but other times for other >> queries it doesn't give me back what I want. Peter> You need to expand on what precisely isn't "as expected". That Peter> is - from your query above I can see DBIC generating the SQL to Peter> satisfy exactly what you asked for. So the disconnect is in you Peter> ot fully understanding the way you formulated the "query" to Peter> DBIC itself. Sorry, I'm expecting to get back consistent results. I've actually given up trying to make multi-step join or prefetch work for me, because I was running out of time. So I ended up de-normalizing my data. Just to refresh the conversation, I have a table with the following two level relationship: Names -> has_many -> Accounts -> has_one -> Boxfolder Where many different accounts can share a Boxfolder row. Basic stuff. So in the end I simply copied the columns from Boxfolder into Accounts and then copied over the data. It's only 20,000 rows, so it's nothing huge and it now works for me. Peter> Please expand on this so I can answer your question in a manner that Peter> will help you generally in the future. I've been looking at the DBIx::Class::Manual::Cookbook at the "Multi-Step prefetch" but since the example doesn't give the relationships, it's hard for me to mentally map what I'm reading in the example code to what I have. This is probably my biggest complaint of all the examples, they just assume to much knowledge. Anyway, I have the following classes, with Name being the only class I search, using the "full_name" column. Name.pm: package Carey::Schema::Result::Name; use base 'DBIx::Class::Core'; __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'); Account.pm: package Carey::Schema::Result::Account; use base 'DBIx::Class::Core'; __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 }, "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("account_id"); __PACKAGE__->belongs_to('name', 'Carey::Schema::Result::Name','name_id'); __PACKAGE__->has_one('boxfolder', 'Carey::Schema::Result::Boxfolder', 'boxfolder_id'); Boxfolder.pm: package Carey::Schema::Result::Boxfolder; use base 'DBIx::Class::Core'; __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','boxfolder_id'); So when I run my test search script, it generates an SQL query using TWO values passed in, both of which are the regexps I'm searching for. Instead of getting something like this query: mysql> SELECT n.name_id,n.full_name,a.account_id,b.boxfolder_id, mysql> b.volume, b.folder, b.range, mysql> substring(a.url,118,locate('&',a.url,118)-118) AS value2 FROM mysql> names as n LEFT JOIN account AS a ON n.name_id = a.name_id LEFT mysql> JOIN boxfolder AS b on a.boxfolder_id = b.boxfolder_id WHERE mysql> n.full_name REGEXP '[[:<:]]carpenter[[:>:]]'; I get this monstrosity: SELECT me.name_id, me.full_name, me.last_name, me.first_name, me.comments, account.account_id, account.account_number, account.boxfolder_id, account.name_id, account.url, account.comments, account.volume, account.box, account.folder, account.range FROM (SELECT me.name_id, me.full_name, me.last_name, me.first_name, me.comments FROM names me WHERE ( full_name REGEXP ? ) ORDER BY full_name ASC LIMIT ?) me LEFT JOIN account account ON account.name_id = me.name_id WHERE ( full_name REGEXP ? ) ORDER BY full_name ASC: '[[:<:]]carpenter[[:>:]]', '10', '[[:<:]]carpenter[[:>:]]' And this is my test code: #!/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 => '[[:<:]]'.$name.'[[:>:]]' }, }, { prefetch => [ 'account' ], order_by => { -asc => 'full_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"; $vol = $a->volume(); $folder = $a->folder(); $range = $a->range(); print " V=$vol " if defined $vol; print " F=$folder " if defined $folder; print " R=$range " if defined $range; print "\n"; } } _______________________________________________ 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/dbix-class@lists.scsys.co.uk