DBI & processing groups of rows

Anonymous Monk has asked for the wisdom of the Perl Monks concerning the following question:

Dear monks,

I need to iterate over a database table, and I need to process them in groups based on a key ("foo_id" in the example). What would be a good perlish way of getting them into an array for processing? Does DBI provide such a function?

This is what I came up with:

my $sth = $dbh->prepare(
  "SELECT foo_id, col1, col2 FROM a ORDER BY foo_id");
$sth->execute();

my @group;

while (my $row = $sth->fetchrow_hashref) {
  if (!@group or $row->{foo_id} == $group[0]->{foo_id}) {
    push @group, $row;
  } else {
    # print Dumper(\@group);
    process(\@group);
    @group = ($row);
  }
}
[download]

but I'm afraid it doesn't process the last group.

Comment on DBI & processing groups of rows Download Code

Replies are listed 'Best First'.
Re: DBI & processing groups of rows by wind (Priest) on Apr 09, 2011 at 21:34 UTC
Nothing wrong with your method, just need to add an extra process call after your while. `process(\@group) if @group;` [download] Also, be sure to add error checking to your dbh executes `$sth->execute() or die $dbh->errstr;` [download]	[reply] [d/l] [select]
Re^2: DBI & processing groups of rows by Anonymous Monk on Apr 10, 2011 at 07:42 UTC
I was really hoping to avoid the "extra" call to process(), as it reeks of code duplication (especially if there are more arguments than just \@group)	[reply]
Re^3: DBI & processing groups of rows by wind (Priest) on Apr 11, 2011 at 05:02 UTC
Well, if you don't care about memory consumption, you can always create all the groups first `my @group; while (my $row = $sth->fetchrow_hashref) { push @group, [] if !@group \|\| $row->{foo_id} == $group[-1][0]{foo_id}; push @{$group[-1]}, $row; } process($_) for (@group);` [download]	[reply] [d/l]
Re^4: DBI & processing groups of rows by Anonymous Monk on Apr 15, 2011 at 06:05 UTC