[Dbix-class] prefetch across multiple tables

[John Stoffel]

>>>>> "Lasse" == Lasse Makholm <lasse at unity3d.com> writes:

Lasse> On Wed, Dec 17, 2014 at 7:39 PM, John Stoffel <john at stoffel.org> wrote:

Lasse>     Hi all,
   
Lasse>     With a bit of nudging, I had a head-smack moment and upgraded to the
Lasse>     latest version of DBIx::Class on CPAN, and it now looks like things
Lasse>     are working better for me, but I'm still getting some interesting
Lasse>     behavior.
   
Lasse>     Before I got errors, now I can do:
   
Lasse>        > ../bin/dbic-test.pl Emlen
Lasse>        DBIx::Class::ResultSet::_construct_results(): Unable to properly collapse has_many results
Lasse>     in iterator mode due to order criteria - performed an eager cursor slurp underneath. Consider
Lasse>     using ->all() instead at ../bin/dbic-test.pl line 20

Lasse> Do you have proper non-nullable primary keys set up in all the
Lasse> relevant result classes?

Maybe... here's my three tables using mysql describe:

  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    |                |
  +--------------+--------------+------+-----+---------+----------------+

I suspect I *might* have a problem, since the Default column shows
NULL for each PRI key defined there.  But a bit of googling seems to
show that I'll be ok here.


Lasse>        Full Name: Emlen & Howell (8213)
Lasse>          account_id=5920 boxfolder_id=234  URL: b16f10
Lasse>          account_id=5921 boxfolder_id=249  URL: b17f10
Lasse>          account_id=5922 boxfolder_id=281  URL: b19f12
Lasse>        Full Name: Emlen, Caleb (8214)
Lasse>          account_id=5923 boxfolder_id=14  URL: b01f14
Lasse>          account_id=5924 boxfolder_id=50  URL: b04f05

Lasse>     So I think I need to change my loop from:
   
Lasse>     while (my $r = $rs->next) {
Lasse>       print "Full Name: ", $r->full_name, " (", $r->name_id, ")\n";
   
Lasse>       foreach my $a ($r->account()) {
Lasse>         print "  account_id=", $a->account_id();
Lasse>         print " boxfolder_id=",$a->boxfolder_id()," ";
Lasse>         my $t = $a->url();
Lasse>         $t =~ m/value2=(\w+)\&/;
Lasse>         print " URL: $1";
Lasse>         $vol = $a->boxfolder->volume;
Lasse>         $folder = $a->boxfolder->volume;
Lasse>         $range = $a->boxfolder->range;
Lasse>         print " V=$vol " if defined $vol;
Lasse>         print " F=$folder " if defined $folder;
Lasse>         print " R=$range " if defined $range;
   
Lasse>         print "\n";
Lasse>       }
Lasse>     }

Lasse>     To something more like this:
   
Lasse>        my @r = $rs->all
Lasse>        foreach my $r (@r) {

Lasse> Be aware that this will cost you in terms of memory because
Lasse> you'll inflate all row objects once.  Especially if you
Lasse> prefetch a lot... Depending on your specific result sets, this
Lasse> may not matter to you... But it's definitely something to be
Lasse> aware of...

Yeah, I think I ran into this.  For the web app side, I really want to
page the results anyway, which is the next step I'm working on, once I
confirm that I'm getting back good search results.

Lasse> We've seen lots of worker processes gobble up 50 - 100 MB of
Lasse> extra memory because of this, so we've been converting most
Lasse> uses of foreach my $row ($rs->all) { ... } to while (my $row =
Lasse> $rs-> next) { ... }. YMMV.

Well.. here's my code that I'm currently using in my Dancer app, which
is based on the Template Toolkit (another thing I'm still learning and
coming upto speed on!).

Now I *know* my query is stupid here.  That's because there's no
actual data in the names table beyond the full_name column and the
primary key.  LOL It's all a conversion from a spreadsheet into a DB.
I think I can get way with:

   full_name REGEXP '[[:<:]]David[[:>:]]'

since it does find more matches, which is a good thing.  

Anyway, my Dancer app looks a little like this:

   sub _perform_search {
     my ($query) = @_;
     my $schema = schema 'Carey';

     # search in Carey
     my @r = $schema->resultset('Name')->search({ -or => [
							  full_name => { like => "$query %" },
							  full_name => { like => "$query,%" },
							  full_name => { like => "% $query" },
							 ]
						},
						{
						 group_by => { -asc => "full_name" },
						 prefetch => { 'accounts' => 'boxfolder' },
						 rows => $limit,
						});
     return @r;
   }


And the template:

    <% FOREACH result IN results %>
      <li><% result.full_name.replace("((?i)$query)",'<b>$1</b>') %>
      <ul>
      <% FOREACH a IN result.accounts %>
        <% b = a.boxfolder %>
        <li><a href="<% a.url %>" target="_blank"> Volume <% a.boxfolder.volume %>, Box <% b.box %>\
, Folder <%
b.folder %>: <% b.range %></a><br> Account_id = <% a.account_id %>,
Boxfolder_id = <% b.boxfolder_id %>, URL = <% a.url %>
      <% END %>
      </ul>
    <% END %>


Thanks,
John