Re^2: Generating a Hash of Hashes Recursively to Display Database Hierarchy

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?

Comment on Re^2: Generating a Hash of Hashes Recursively to Display Database Hierarchy Select or Download Code

Replies are listed 'Best First'.
Re^3: Generating a Hash of Hashes Recursively to Display Database Hierarchy by ikegami (Patriarch) on Jun 23, 2009 at 16:23 UTC
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]
Re^4: Generating a Hash of Hashes Recursively to Display Database Hierarchy by c4onastick (Friar) on Jun 23, 2009 at 16:59 UTC
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]
Re^5: Generating a Hash of Hashes Recursively to Display Database Hierarchy by ikegami (Patriarch) on Jun 23, 2009 at 17:17 UTC
With the top: `while (my $row = $sth->fetch()) { for (1..$#$row) { my ($parent_id, $id) = @{$row}[$i-1, $i-0]; last if !defined($id); ... } }` [download] With the bottom: `while (my $row = $sth->fetch()) { my ($parent_id, $id) = @$row; ... }` [download]	[reply] [d/l] [select]