sorting by prior value in a deeply nested hash

[ccc31807]

I have a reference to a hash defined like this:
$chpr->{$college}{$dept}{$rank}{$fac_id}{$subj} = {
...,
sort_name = $sort_name,
...};

I print an Excel report like this:
#open Excel workbook for COLLEGE
foreach my $college (sort keys %{$chpr}) {
foreach my $dept (sort keys %{$chpr->{$college}}) {
foreach my $rank (sort keys %{$chpr->{$college}{$dept}}) {
#open new worksheet for DEPARTMENT
foreach my $fac_id (sort keys %{$chpr->{$college}{$dept}{$rank}}) {
foreach my $subj (sort keys %{$chpr->{$college}{$dept}{$rank}{$fac_id}}) {
#$worksheet->write_row($row, $col, $arrayref);
#here, I would like to alphabetically sort by
#$chpr->{$college}{$dept}{$rank}{$fac_id}{$subj}{sort_name}
#so that the faculty names appear in alphabetical order listed
#by college, department, rank, and subject (faculty ID isn't wanted)
}
}
}
}
}

I can't see the $subj from $fac_id, and once I get to $subj, the has is already sorted by $fac_id. I can't figure out a way to retroactively unsort by $fac_id and sort by ...{$fac_id}...{sort_name}.

Ordering the faculty by sort_name isn't possible because sort_name is not unique, but fac_id is unique.

Suggestions? Other than rewriting the whole thing?

Thanks, CC.

 

[Jim Gibson]

I have a reference to a hash defined like this:
$chpr->{$college}{$dept}{$rank}{$fac_id}{$subj} = {
...,
sort_name = $sort_name,
...};

I print an Excel report like this:
#open Excel workbook for COLLEGE
foreach my $college (sort keys %{$chpr}) {
foreach my $dept (sort keys %{$chpr->{$college}}) {
foreach my $rank (sort keys %{$chpr->{$college}{$dept}}) {
#open new worksheet for DEPARTMENT
foreach my $fac_id (sort keys %{$chpr->{$college}{$dept}{$rank}}) {
foreach my $subj (sort keys %{$chpr->{$college}{$dept}{$rank}{$fac_id}}) {
#$worksheet->write_row($row, $col, $arrayref);
#here, I would like to alphabetically sort by
#$chpr->{$college}{$dept}{$rank}{$fac_id}{$subj}{sort_name}
#so that the faculty names appear in alphabetical order listed
#by college, department, rank, and subject (faculty ID isn't wanted)
}
}
}
}
}

I can't see the $subj from $fac_id, and once I get to $subj, the has is
already sorted by $fac_id. I can't figure out a way to retroactively unsort
by $fac_id and sort by ...{$fac_id}...{sort_name}.

Ordering the faculty by sort_name isn't possible because sort_name is not
unique, but fac_id is unique.

Suggestions? Other than rewriting the whole thing?

I would stop at the $rank iteration and create a temporary data
structure that holds the data you want to print for that combination of
($college,$dept,$rank). I think you want to basically invert the hash
array for that point onward.

If you have a hash called %data, then iterate over $fac_id and $subj,
saving the data as $data{$subj}{$fac_id}. Then, when you are done, you
can iterate over subjects, iterate over faculty name, and print.

I might also create a %faculty_names hash using $faculty_name{$fac_id}
= sort_name for looking up the faculty name given the ID.

I would also recommend saving each hash reference in a scalar variable,
thereby getting rid of the multi-level hash fetches:

foreach my $college (sort keys %{$chpr}) {
my $college_ref = $chpr->{$college};
foreach my $dept (sort keys %{$college_ref} ) {
my $dept_ref = $college_ref->{$dept};
...