in reply to Generating a Hash of Hashes Recursively to Display Database Hierarchy

If you're ok with changing 

return unless ref($hashref) eq 'HASH';

[download]

to 

return unless keys %$hashref;

[download]

then you can use the following: 

sub treeify {
    my ($sth) = @_;

    my %root;
    my %children;

    while (my $row = $sth->fetch()) {
        my ($id, $parent_id) = @$row;

        my $parent = ( defined($parent_id)
            ? $children{$parent_id} ||= {}
            : \%root
        );

        $parent->{$id} = $children{$id} ||= {};
    }

    return \%root;
}

[download]

Test: 

{
    my $sponge = DBI->connect(
        'dbi:Sponge:', '', '',
        { RaiseError => 1 }
    );

    my $sth = $sponge->prepare(
        'SELECT id, parent_id FROM Table',
        {
            NAME => [qw( id parent_id )],
            rows => [
                [ a => undef ],
                [ b => 'a'   ],
                [ d => 'c'   ],
                [ c => 'b'   ],
                [ e => 'a'   ],
            ],
        }
    );

    my $tree = treeify($sth);

    use Data::Dumper;
    print Dumper $tree;
}

[download]

Output: 

$VAR1 = {
          'a' => {
                   'e' => {},
                   'b' => {
                            'c' => {
                                     'd' => {}
                                   }
                          }
                 }
        };

[download]

Replies are listed 'Best First'.
Re^2: Generating a Hash of Hashes Recursively to Display Database Hierarchy
by c4onastick (Friar) on Jun 23, 2009 at 15:17 UTC

    Thanks ikegami! Unfortunately the database I'm dealing with doesn't quite let me simplify down to just parent-child IDs. The join I mentioned above is between about 10 tables, all with distinct sets of IDs, so the result of the join, as retrieved by selectall_arrayref looks like:

    my $data = [
   [ 'parent1', 'child1a', 'child2a', undef,    ],
   [ 'parent1', 'child1b', 'child2b', 'child3b',],
   [ 'parent1', 'child1c', undef, undef,],
];

    [download]

    While playing with your solution, I got really close to what I need:

    my @results;
foreach my $row (@$data) {
   push @results, treeify(@$row);
}

print Dumper @results;

sub treeify {
   my @list = @_;
   my $node = shift @list;
   return {} unless $node;
   return {$node => treeify(@list)};
}

    [download]

    Which yields:

    $VAR1 = {
          'parent1' => {
                         'child1a' => {
                                        'child2a' => {}
                                      }
                       }
        };
$VAR2 = {
          'parent1' => {
                         'child1b' => {
                                        'child2b' => {
                                                       'child3b' => {}
                                                     }
                                      }
                       }
        };
$VAR3 = {
          'parent1' => {
                         'child1c' => {}
                       }
        };

    [download]

    Which sort of brings me back to my original query, figuring out how to merge those three hashes.

    Any thoughts?

[reply]
[d/l]
[select]

      I dare say your join is broken and that's what should be fixed. But it's easy to work with it anyway.

      You have: 

      my $data = [
   [ 'parent1', 'child1a', 'child2a', undef,    ],
   [ 'parent1', 'child1b', 'child2b', 'child3b',],
   [ 'parent1', 'child1c', undef, undef,],
];

      [download]

      You want: 

      my $data = [
   [ 'parent1', 'child1a' ],
   [ 'parent1', 'child2a' ],
   [ 'parent1', 'child1b' ],
   [ 'parent1', 'child2b' ],
   [ 'parent1', 'child3b' ],
   [ 'parent1', 'child1c' ],
];

      [download]

      Easy! Change 

          while (my $row = $sth->fetch()) {
        my ($id, $parent_id) = @$row;
        ...
    }

      [download]

      to 

          while (my $row = $sth->fetch()) {
        my ($parent_id, @children_ids) = @$row;
        for my $id (grep defined, @children_ids) {
            ...
        }
    }

      [download]
[reply]
[d/l]
[select]

        Ah, my apologies! My short hand with $data got a little too short! $data really looks like this:

        my $data = [
   [ 'parent1', 'child1', 'grand_child1', undef,    ],
   [ 'parent1', 'child2', 'grand_child2', 'great_grand_child1',],
   [ 'parent1', 'child3', undef, undef,],
];

        [download]

        Or:

        my $data = [
   [ 'parent1',      'child1' ],
   [ 'child1',       'grand_child1' ],
   [ 'parent1',      'child2' ],
   [ 'child2',       'grand_child2' ],
   [ 'grand_child2', 'great_grand_child2' ],
   [ 'parent1',      'child1c' ],
];

        [download]

        But because of our extended table structure, I'm sure how I can (easily) make it look like the second form though.

[reply]
[d/l]
[select]

In Section Seekers of Perl Wisdom