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

Ok, in a nutshell, I'm trying to convert this: 

my $hash_ref;
while ( my $row = $sth->fetchrow_hashref ) {
  push @{ $hash_ref->{ $row->{ 'msgid' } } }, 
    { 'option'      => $row->{ 'option' }, 
      'option_data' => $row->{ 'option_data' } };
}

[download]
(which works exactly as I want)

To this: 

my $rows = $sth->fetchall_arrayref;
my %hash = map { 
  $_->[0] => { 
    'option'        => $_->[1], 
    'option_data'   => $_->[2]
  };
} @{$rows};

[download]
(which doesn't work, but iterates everything in < 10 seconds as opposed to the other way which is around 150 seconds to iterate all the rows.)

The details...
I'm trying to take a table that looks like this:
idoptionoption_data
1234foofooval
1235barbarval
1236bazbazval
1234quuxquuxval
Notice the repeated 1234, with different option and option_data.

So, like the results of the while loop, I'd like the map to end up with this: 

'1234' => [
  {
    'option_data' => 'fooval',
    'option' => 'foo'
  },
  {
    'option_data' => 'quuxval',
    'option' => 'quux'
  }
],
'1235' => [
  {
    'option_data' => 'barval',
    'option' => 'bar'
  }
],
'1236' => [
  {
    'option_data' => 'bazval',
    'option' => 'baz'
  }
]

[download]

--
"A long habit of not thinking a thing wrong, gives it a superficial appearance of being right." -- Thomas Paine
naChoZ

Replies are listed 'Best First'.
Re: while/push to map, data structure confusion
by dragonchild (Archbishop) on Jul 30, 2004 at 20:22 UTC 
    # Do your prepare() and execute() up here ...

$sth->bind_columns( \my( $id, $option, $data) );

my %stuff;
while ($sth->fetch)
{
    push @{$stuff{$id}}, {
        option => $option,
        option_data => $data,
    };
}

    [download]

    That's how the DBI docs say to optimize a fetch.

    ------
    We are the carpenters and bricklayers of the Information Age.

    Then there are Damian modules.... *sigh* ... that's not about being less-lazy -- that's about being on some really good drugs -- you know, there is no spoon. - flyingmoose

    I shouldn't have to say this, but any code, unless otherwise stated, is untested

[reply]
[d/l]
Re: while/push to map, data structure confusion
by Roy Johnson (Monsignor) on Jul 30, 2004 at 21:26 UTC
    Notwithstanding the fine answer you've already received, if you still want to do it with a fetchall, you can't build the hash with map (okay, you probably can, but not in a conventional way) because you're not building a straightforward hash, where each input element corresponds to a hash member. Instead, you're building a compound hash where each input element gets pushed onto a hash member.

    This example code does the transformation you want. 

    #!perl
use Data::Dumper;

$rows = [[qw(1234 foo fooval)],[qw(1235 bar barval)],[qw(1234 quux quu
+xval)]];
my %hash;
for (@$rows) {
  push @{$hash{$_->[0]}}
  , { option      => $_->[1]
    , option_data => $_->[2]
    }
  ;
}

print Dumper(\%hash);

    [download]
    Update: which, of course, looks very similar to dragonchild's solution, because it's doing basically the same thing.
    We're not really tightening our belts, it just feels that way because we're getting fatter.
[reply]
[d/l]
Re: while/push to map, data structure confusion
by diotalevi (Canon) on Jul 30, 2004 at 21:28 UTC

    Say, while dragonchild answered your question I also see confusion on your part about when to use map. It isn't appropriate now because the end result joins some elements but leaves others separate. Its about what the granularity is. When your "unit" is larger than a single input element to map() then map isn't going to be able to join them for you. The map's focus is on transforming each element into something else for use as a return value. while/fetch's focus is just on getting through to the end and then you do the work of combining things as necessary.

[reply]
Re: while/push to map, data structure confusion
by JamesNC (Chaplain) on Jul 31, 2004 at 23:39 UTC
    It is a matter of style perhaps, but here is how I prefer to grab the results out of DBI (I usually boil it down to 4 lines but I added some extra info for newbies that might read this :-) 
    use DBI;
use strict;

# declare holder for our record set and some sample sql
my @results;
my $sql = "select * from msazip where zip = 60604";

# connect to sql server
my $dbh = DBI->connect("dbi:ODBC:mysql", "username", "pass", { RaiseEr
+ror=>1 } ) or die "$!";

# prepare and send the query
my $sth = $dbh->prepare( $sql ) or die "$!";  $sth->execute();

# get the column names if you don't know them already
my @col_names = @{ $sth->{NAME}};

# build an array of hashrefs so we can preserve order of record set
while( my $hr = $sth->fetchrow_hashref ){ push @results, $hr; }

# now use them - in this case just print each row
# $rs->{'col_name'} or $results[0]->{'col_name'}

foreach my $rs (@results ){
  print ( sprintf "%-15s", $rs->{$_} ), " " foreach @col_names;
  print "\n";
}

    [download]
    To me it is much clearer to simply push the hashrefs into an array ( you can be sure to preserve the order of your result set ) than it is to try to do something with the likes of map or trying to build some complicated structure, keep it simple.

    I am thinking something other than how you are storing the result set is really the problem for you and it came from your assertion that using fetchrow_hashref is vastly slower than using the fetchall_arrayref( 150 seconds to fetchrow_hashref -vs- 10 seconds using fetchall_arrayref method. ) I somehow doubted that is really the case. When I see really slow queries, the first place I check is my query and then the database. I am thinking the table is either missing an index or is not indexed to optimize your query. If you run a query on some modern rdbms's where a table is missing an index, some will cache the results and the next time you run it with the same params it will try to optimize it for you. Hence one time through you get a query that takes a long time and the next it seems a lot faster. Perhaps this is really the case. I would look to answer these questions: How is the table indexed? Does it even have an index!! Create a new index based on your query. Don't make storing your result set complicated. Hope this helps.

    JamesNC
[reply]
[d/l]
Re: while/push to map, data structure confusion
by bageler (Hermit) on Jul 31, 2004 at 19:54 UTC
    A good way to resolve data structure confusion is to use Data::Dumper from CPAN and view your data structure: 
    use Data::Dumper;
print STDERR "This is confusing: ",Dumper($someConfusingStructure),"\n
+";

    [download]

    Then you can see exactly what you're dealing with and perhaps come up with a solution faster than you would be asking the general public.

    In any case, the above posters are correct that a map cannot solve your problem without making things slower and more complicated. I can see it being done by nesting maps and putting extra conditions inside the map block, but that's much slower than a single loop and pushing arrays.
[reply]
[d/l]
Re: while/push to map, data structure confusion
by naChoZ (Curate) on Aug 01, 2004 at 12:45 UTC

    Thanks for the great answers guys. diotalevi is right, I'm still no master of map. One of the things I'm doing to try to learn map is to take things that I already can do and see if map can do it faster.

    I ended up going with Ray Johnson's solution because it runs the fastest. It does exactly what I want in about 30 seconds, where using fetchrow methods add about an extra minute.

    --
    "A long habit of not thinking a thing wrong, gives it a superficial appearance of being right." -- Thomas Paine
    naChoZ

[reply]

Back to Seekers of Perl Wisdom