perl group results in hash using fetchrow_hashref

Question

I want to group my results by countryid with the data shown below.

my @test = ();
my $st   = qq[
    SELECT id,countryID,abbrev
    FROM myTable
];
my $q = $data->prepare($st);
$q->execute() or query_error($st);
while ( my $result = $q->fetchrow_hashref ) {
    push @test, $result;
}

Using fetchrow_hashref I have no problem displayling the results

use Data::Dumper;
print STDERR Dumper(\@test);

returns

$VAR1 = [
    {   'id'        => '1',
        'countryID' => '1',
        'title'     => 'Title 1',
        'abbrev'    => 't1'
    },
    {   'id'        => '2',
        'countryID' => '2',
        'title'     => 'Title 2',
        'abbrev'    => 't2'
    },
    {   'id'        => '3',
        'countryID' => '3',
        'title'     => 'Title 3',
        'abbrev'    => 't3'
    },
    {   'id'        => '4',
        'countryID' => '1',
        'title'     => 'Title 4',
        'abbrev'    => 't4'
    }
];

I want to group it by countries as shown below.

$VAR1 = [
    'countries' => {
        '1' => [
            {   'id'     => '1',
                'title'  => 'Title 1',
                'abbrev' => 't1'
            },
            {   'id'     => '4',
                'title'  => 'Title 4',
                'abbrev' => 't4'
            }
        ],
        '2' => [
            {   'id'     => '2',
                'title'  => 'Title 2',
                'abbrev' => 't2'
            }
        ],
        '3' => [
            {   'id'     => '3',
                'title'  => 'Title 3',
                'abbrev' => 't3'
            }
        ]
    }
];

How can I get this working in the while loop?

score 2 · Answer 1 · edited May 23 '17 at 12:06

Ignoring the errors in your sample data structure, you are basically looking to convert form array of hashes to hash of hash of array of hashes. Once you have your initial data structure setup, you can do the following to create your new nested data structure:

for my $href ( @test ) {
    my $id = $href->{countryID};
    delete $href->{countryID};
    push @{ $test2->{countries}{$id} }, $href;
}

Iterate each element of your array @test which basically is an array of hash references. Create a variable $id which will capture the countryID value from the hash. We delete it from the hash reference and then assign that hash reference to our new nested data structure which has countries as the first level key and the $id as the second level key.

We use push syntax to create our array of such references.

Note: As stated by thb in the comments, this does destroys your original data structure. If you'd like to retain the original structure, modify the code to the following:

for my $href ( @test ) {
    my $copy = { %$href };
    my $id   = $copy->{countryID};
    delete $copy->{countryID};
    push @{ $test2->{countries}{$id} }, $copy;
}

Your answer is good, but I would make a small remark: Besides populating `%$test2`, doesn't your code inadvertently trash `@test`? If so, this may be fine, but perhaps it should be pointed out. — thb, Sep 15 '14 at 04:14
Thanks @thb for the feedback. I have added your comment to the answer and suggested an alternative solution to evade the issue. — jaypal singh, Sep 15 '14 at 04:44

score 1 · Answer 2 · edited Sep 15 '14 at 05:35

Something like this, the input/output data structures might not be exactly what you have or want, you can patch that up.

use strict;
use Data::Dumper;

$a = [
    {   'id'        => '1',
        'countryID' => '1',
        'title'     => 'Title 1',
        'abbrev'    => 't1'
    },
    {   'id'        => '2',
        'countryID' => '2',
        'title'     => 'Title 2',
        'abbrev'    => 't2'
    },
    {   'id'        => '3',
        'countryID' => '3',
        'title'     => 'Title 3',
        'abbrev'    => 't3'
    },
    {   'id'        => '4',
        'countryID' => '1',
        'title'     => 'Title 4',
        'abbrev'    => 't4'
    }
];

my $b = {};

for my $item (@$a) {
    if ( exists( $b->{ $item->{'countryID'} } ) ) {
        push( @{ $b->{ $item->{'countryID'} } }, $item );
    } else {
        $b->{ $item->{'countryID'} } = [$item];
    }
}
print Dumper($b);

The above prints:

$VAR1 = {
    '1' => [
        {   'abbrev'    => 't1',
            'title'     => 'Title 1',
            'id'        => '1',
            'countryID' => '1'
        },
        {   'abbrev'    => 't4',
            'title'     => 'Title 4',
            'id'        => '4',
            'countryID' => '1'
        }
    ],
    '3' => [
        {   'abbrev'    => 't3',
            'title'     => 'Title 3',
            'id'        => '3',
            'countryID' => '3'
        }
    ],
    '2' => [
        {   'abbrev'    => 't2',
            'title'     => 'Title 2',
            'id'        => '2',
            'countryID' => '2'
        }
    ]
};

thb · Accepted Answer · 2014-09-15T04:56:19.790

You'll need to fix your syntax above a little (for example => instead of = >), but once you have done that, something like this should work nicely.

for (@$VAR1_orig) {
    my %a = %$_;
    my $countryID = $a{countryID};
    delete $a{countryID};
    push @{$VAR1->{countries}{$countryID}}, \%a;
}

(I have tried it on my computer, incidentally. It works.)

The above assumes that %$VAR1 is initially empty, then populates it according to @$VAR1_orig, after which you can do with $VAR1 whatever you like. (I assume that you know what %$ and @$ mean in Perl, but this is not a beginner's topic, as you may know. See man 1 perlref.)

perl group results in hash using fetchrow_hashref

3 Answers3