Fixing my relationships to get working queries

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
7 messages Options
Reply | Threaded
Open this post in threaded view
|

Fixing my relationships to get working queries

John Stoffel

Hi all,

I'm a DBIx::class newbie who is trying to write a web search app using
the perl Dancer web framework with the Dancer::Plugin::DBIC and having
some problems.  I've got some hand written SQL which seems to do what
I want, and I've attempted to setup my schema properly to do my
queries, but it's not working.  

Working SQL is:

   mysql> SELECT n.name_id,n.full_name,a.account_id,b.boxfolder_id,b.volume,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 | volume | folder | range     | value2  |
   +---------+----------------------------+------------+--------------+--------+--------+-----------+---------+
   |    7220 | Byrne, E. & McCausland A.  |       2642 |          322 | 31     | 8      | 5123-5148 | =b22f08 |
   |    7221 | Byrne, G.                  |       2643 |          295 | 29     | 11     | 4293-4314 | =b20f11 |
   |    7222 | Byrne, John                |       2644 |          106 | 16     | 1      | 6003-6128 | =b08f01 |
   |    7223 | Byrne, P.                  |       2645 |          425 | 37     | fol03  |           | =fol03  |
   |    7224 | Byrne, P., Estate of       |       2646 |          425 | 37     | fol03  |           | =fol03  |
   |    7225 | Byrne, Patrick             |       2672 |          210 | 23     | 1      | 1404-1433 | =b15f01 |
   |    7225 | Byrne, Patrick             |       2673 |          210 | 23     | 1      | 1404-1433 | =b15f01 |
   |    7225 | Byrne, Patrick             |       2674 |          214 | 23     | 5      | 1502-1538 | =b15f05 |
   |    7225 | Byrne, Patrick             |       2675 |          215 | 23     | 6      | 1539-1596 | =b15f06 |
   |    7225 | Byrne, Patrick             |       2676 |          215 | 23     | 6      | 1539-1596 | =b15f06 |
   +---------+----------------------------+------------+--------------+--------+--------+-----------+---------+
   10 rows in set (0.14 sec)


And my DBIC query ends up looking like this:

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, boxfolder.boxfolder_id, boxfolder.volume, boxfolder.box, boxfolder.folder, boxfolder.range, boxfolder.comments 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 LEFT JOIN boxfolder boxfolder ON boxfolder.boxfolder_id = account.account_id WHERE ( full_name REGEXP ? ) ORDER BY full_name ASC: '[[:<:]]Byrne[[:>:]]', '20', '[[:<:]]Byrne[[:>:]]'


Which is obviously not correct, or at least it's sub-optimal.  My tables are 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_increment |
    | 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    |                |
    +--------------+--------------+------+-----+---------+----------------+


The Schema files were generated with the DBIx::Class::Schema::Loader


And this is the following schema I'm using in my perl code:

  # Show SQL query
  $schema->storage->debug(1);

  # log to a specific file
  $schema->storage->debugfh(IO::File->new('/tmp/carey.out','w'));
 
  # 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.  The Schema looks like
this:

Name.pm:

  # Created by DBIx::Class::Schema::Loader v0.07025 @ 2014-11-09 21:26:06
  # DO NOT MODIFY THIS OR ANYTHING ABOVE! md5sum:phDO23qEE/ccn+lnwUG4Cw

  __PACKAGE__->has_many('account', 'Carey::Schema::Result::Account','name_id');

Account.pm:

  # Created by DBIx::Class::Schema::Loader v0.07025 @ 2014-11-09 21:26:06
  # DO NOT MODIFY THIS OR ANYTHING ABOVE! md5sum:z+bO9XivBmPGrD9BmbSaRg

  __PACKAGE__->belongs_to('name', 'Carey::Schema::Result::Name','account_id');
  __PACKAGE__->has_one('boxfolder', 'Carey::Schema::Result::Boxfolder','boxfolder_id' );


Boxfolder.pm:

  # Created by DBIx::Class::Schema::Loader v0.07025 @ 2014-11-09 21:26:06
  # DO NOT MODIFY THIS OR ANYTHING ABOVE! md5sum:0niCoJ/aJgMl1QYT77b2sA

  __PACKAGE__->belongs_to('account','Carey::Schema::Result::Account','boxfolder_id');


The basic idea is that I do all my searching on the Names table,
against 'full_name'.  I'm doing a regexp since they want word based
matches and this was simpler thank a bunch of LIKE ... OR LIKE ... OR
LIKE... constructs.  I'm sure it's not optimal, but it's not a huge
DB in terms of the number of rows.  I'm trying to make it work.

So my pure perl/DBIC has the same problem, it doesn't give me back the
results I expect so I'm sure I've done something wrong in my Schema,
which is throwing things off.

Does anyone have any pointers on what I'm doing wrong here?

Thanks,
John

_______________________________________________
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@...
Reply | Threaded
Open this post in threaded view
|

Re: Fixing my relationships to get working queries

John Stoffel
>>>>> "Peter" == Peter Rabbitson <[hidden email]> 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@...
Reply | Threaded
Open this post in threaded view
|

Re: Fixing my relationships to get working queries

John Stoffel

Peter> On 01/19/2015 06:41 PM, John Stoffel wrote:
>>
>> Sorry, I'm expecting to get back consistent results.

Peter> I am not entirely sure how to interpret that... :)

Me too... and I wrote it!  I did see that the list had issues over the
Christmas break, so I'm sure we'll a bit behind.  I've got a cold now
which is also slowing my brain down.  

>> I've actually
>> given up trying to make multi-step join or prefetch work for me,
>> because I was running out of time.

Peter> Sorry about that - the mailing list had issues, I only received your
Peter> emails today.

No problem, if I can make it work properly, I'd be happy to use it
since I do think it makes more sense, but I can survive with what I've
got now I think.

>> Just to refresh the conversation,
>>
>> ...
>>
>> 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[[:>:]]'

Peter> This (as you called it) "monstrosity" is there for a good reason.
Peter> However I can't really explain it without the actual code that produced
Peter> it (hint - the thing below is *not* what produced the above query - it
Peter> is missing a rows => spec)

>>
>> my $rs = $schema->resultset('Name')->search({ full_name => { regexp =>
>> '[[:<:]]'.$name.'[[:>:]]' },
>> },
>> {
>> prefetch => [ 'account' ],
>> order_by => { -asc => 'full_name' },
>> });

Peter> If you have time and want to really understand what was going on - let's
Peter> start over with an actual code/query example.

Ok.  Here's an example.  I'm looking for all matches in full_name for
the name "carpenter".  In this case, I'm not limiting the rows
returned because I know I'll only get two matches.  I also want to
prefetch all the data from the Account and Boxfolder tables.  To do
this, I use the following mysql 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[[:>:]]';
    +---------+----------------------+------------+--------------+--------+--------+-----------+---------+
    | name_id | full_name            | account_id | boxfolder_id | volume | folder | range     | value2  |
    +---------+----------------------+------------+--------------+--------+--------+-----------+---------+
    |    7333 | Carpenter, John      |       3176 |          423 | 35     | fol01  |           | =fol01  |
    |    7333 | Carpenter, John      |       3177 |          423 | 35     | fol01  |           | =fol01  |
    |    7333 | Carpenter, John      |       3178 |          423 | 35     | fol01  |           | =fol01  |
    |    7334 | Carpenter, John Jr.  |       3179 |          106 | 16     | 1      | 6003-6128 | =b08f01 |
    |    7334 | Carpenter, John Jr.  |       3180 |          112 | 16     | 7      | 6780-6806 | =b08f07 |
    |    7334 | Carpenter, John Jr.  |       3181 |          122 | 17     | 2      | 6999-7125 | =b09f02 |
    |    7334 | Carpenter, John Jr.  |       3182 |          122 | 17     | 2      | 6999-7125 | =b09f02 |
    +---------+----------------------+------------+--------------+--------+--------+-----------+---------+

The substring stuff is just because the URL is long and mostly
redundant.  I didn't enter the data, so I'm just working around it in
this example.

For my perl code, to get the same result, I was trying to use the
following perl code, which removes a bunch of setup code to make it
smaller:

    my $rs = $schema->resultset('Name')->search({ full_name =>
                                                  { regexp => '[[:<:]]'.$name.'[[:>:]]' }
                                                },
                                                {
                                                 prefetch => [ 'account' ],
                                                 rows => 10,
                                                 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";
      }
    }


And when I run the code, I got:

   > ../bin/dbic-test2.pl carpenter
   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[[:>:]]'
   Full Name: Carpenter, John  (7333)
     account_id=3176 boxfolder_id=423  URL: fol01 V=35  F=fol01  R=
     account_id=3177 boxfolder_id=423  URL: fol01 V=35  F=fol01  R=
     account_id=3178 boxfolder_id=423  URL: fol01 V=35  F=fol01  R=
   Full Name: Carpenter, John Jr.  (7334)
     account_id=3179 boxfolder_id=106  URL: b08f01 V=16  F=1  R=6003-6128
     account_id=3180 boxfolder_id=112  URL: b08f07 V=16  F=7  R=6780-6806
     account_id=3181 boxfolder_id=122  URL: b09f02 V=17  F=2  R=6999-7125
     account_id=3182 boxfolder_id=122  URL: b09f02 V=17  F=2  R=6999-7125


And I'm completely confused why there are multiple SELECTs since the
whole idea of prefetch was to just pull in the info ahead of time even
though I'm not searching against it.

Do you want me to send you more details on the Schema I have?  

Thanks,
John

_______________________________________________
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@...
Reply | Threaded
Open this post in threaded view
|

Re: Fixing my relationships to get working queries

John Stoffel
>>>>> "Peter" == Peter Rabbitson <[hidden email]> writes:

Peter> The question as you wrote it is good, I just need to clarify some extra
Peter> things before I answer it:

Peter> On 01/19/2015 07:23 PM, John Stoffel wrote:

>>
>>
>> > ../bin/dbic-test2.pl carpenter
>> 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[[:>:]]'
>> Full Name: Carpenter, John  (7333)
>> account_id=3176 boxfolder_id=423  URL: fol01 V=35  F=fol01  R=
>> account_id=3177 boxfolder_id=423  URL: fol01 V=35  F=fol01  R=
>> account_id=3178 boxfolder_id=423  URL: fol01 V=35  F=fol01  R=
>> Full Name: Carpenter, John Jr.  (7334)
>> account_id=3179 boxfolder_id=106  URL: b08f01 V=16  F=1  R=6003-6128
>> account_id=3180 boxfolder_id=112  URL: b08f07 V=16  F=7  R=6780-6806
>> account_id=3181 boxfolder_id=122  URL: b09f02 V=17  F=2  R=6999-7125
>> account_id=3182 boxfolder_id=122  URL: b09f02 V=17  F=2  R=6999-7125

Peter> You show your script as executing one query (there is only one line
Peter> above), yet further down you say:

>> And I'm completely confused why there are multiple SELECTs since the
>> whole idea of prefetch was to just pull in the info ahead of time

I'm talking about the multiple SELECT statements all mashed together
into the query above which stars with:  SELECT me.name_id,
me.ful_name....  

Why does it need to use TWO regexp matches in the query?  The one I
did by hand up above seems (ha!!) to be more efficient and certainly
clearer about what I'm trying to do.  And it's much more of what I
would expect from the info provided in:

  http://search.cpan.org/dist/DBIx-Class/lib/DBIx/Class/Manual/Cookbook.pod#JOINS_AND_PREFETCHING


Peter> Um... yes - which one is it? Did yoiu get a single SELECT statement or
Peter> multiple?

So maybe I should have said "a query with multiple SELECTs" in it,
when only one is needed.  


_______________________________________________
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@...
Reply | Threaded
Open this post in threaded view
|

Re: Fixing my relationships to get working queries

Alexander Hartmaier
In reply to this post by John Stoffel
On 2015-01-19 18:41, John Stoffel wrote:

>>>>>> "Peter" == Peter Rabbitson <[hidden email]> 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.
Seems like one of the more often happening mistakes: the has_one should
be a belongs_to instead because Accounts stores the primary key of a
Boxfolder.

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



*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*
T-Systems Austria GesmbH Rennweg 97-99, 1030 Wien
Handelsgericht Wien, FN 79340b
*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*
Notice: This e-mail contains information that is confidential and may be privileged.
If you are not the intended recipient, please notify the sender and then
delete this e-mail immediately.
*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*

_______________________________________________
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@...
Reply | Threaded
Open this post in threaded view
|

Re: Fixing my relationships to get working queries

John Stoffel
>>>>> "Hartmaier" == Hartmaier Alexander <[hidden email]> writes:

Hartmaier> On 2015-01-19 18:41, John Stoffel wrote:
>>>>>>> "Peter" == Peter Rabbitson <[hidden email]> 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.

Hartmaier> Seems like one of the more often happening mistakes: the
Hartmaier> has_one should be a belongs_to instead because Accounts
Hartmaier> stores the primary key of a Boxfolder.

It is a mistake on my part, but only in the above section.  I think I
have it correctly setup in the __PACKAGE section of Boxfolder.pm, as
shown below:

>> 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 I'm stumped honestly.  Something I'm doing is incorrect here, but I
can't figure it out.  I'll poke at this more later.

John

_______________________________________________
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@...
Reply | Threaded
Open this post in threaded view
|

Re: Fixing my relationships to get working queries

Peter Rabbitson-2
On 01/20/2015 06:23 PM, John Stoffel wrote:
>
> So I'm stumped honestly.  Something I'm doing is incorrect here, but I
> can't figure it out.  I'll poke at this more later.

Let's step back and address just the last part of my latest reply:
http://lists.scsys.co.uk/pipermail/dbix-class/2015-January/011891.html
The "N things with all their (unknown number) of relations" explanation
- is it still confusing? Can you try to point out from which point on do
you get lost?

Cheers


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