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?

Answer 1

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.)