>>>>> "Lasse" == Lasse Makholm <[email protected]> writes:
Lasse> On Wed, Dec 17, 2014 at 7:39 PM, John Stoffel <[email protected]> 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 _______________________________________________ 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/[email protected]
