[Dbix-class] Fixing my relationships to get working queries

[John Stoffel]

>>>>> "Peter" == Peter Rabbitson <rabbit+dbic at 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";
  }
}