use strict;
use warnings;
use DBI;
sub treeify {
my ($sth) = @_;
my %root = ( _children => [] );
my %children;
while (my $row = $sth->fetchrow_hashref()) {
my ($id, $parent_id) = @$row{qw( Child Parent )};
my $parent = ( defined($parent_id)
? $children{$parent_id} ||= { _children => [] }
: \%root
);
my $node = $children{$id} ||= {};
%$node = ( _children => [], %$row );
push @{ $parent->{_children} }, $node;
}
return \%root;
}
{
my $sponge = DBI->connect(
'dbi:Sponge:', '', '',
{ RaiseError => 1 }
);
my $sth = $sponge->prepare(
'SELECT * FROM Fruit',
{
NAME => [qw( Parent Child Desc Property1
+Property2 )],
rows => [ [ undef, 50, 'Fruit', 'hidden',
+'non-searchable' ],
[ 50, 100, 'Apple', 'hidden',
+'non-searchable' ],
[ 100, 110, 'Granny Smith', 'Visible',
+'searchable' ],
],
}
);
my $tree = treeify($sth);
use Data::Dumper;
print Dumper $tree;
}
Based on Generating a Hash of Hashes Recursively to Display Database Hierarchy. Should use a classes.