I solved this problem when I wrote
Cascade, which uses a Postgres backend to manage an index of resources. The issue is the same-- you have a tree of categories that you want to organize. You can download the source code
here. I recommend version 1.3.7 at the moment for your purposes. (although it's not to be considered "stable").
In short, I used a different data model which made things easier for me. It's basically a adaptation of what Phillip Greenspun describes here. It allows you to select all your messages in threaded ordered with one select statement, which I like.
To save you a bit of looking, here's my routine that creates a list much like what you are doing above. This of course depends on having your data model set up as mine is.
# used for selecting a new category to attach this item to.
sub _new_category_id_box {
my $item = shift;
my $name = shift;
my $tbl = $Cascade::DBH->selectall_arrayref("
SELECT id, name, (length(sort_key)-2) as level
FROM category
ORDER BY sort_key");
my %parents;
my $q = new CGI;
my @p_ids;
foreach my $row (@$tbl) {
my ($id,$name,$level) = @$row;
$parents{$id} = ' 'x$level.CGI::escapeHTML($name);
push @p_ids, $id; # we keep the ids in a seperate array to pre
+serve sorting
}
$q->autoEscape(undef); # we need to turn this off for the spaces i
+n the list to show up correctly.
return $q->scrolling_list(
-name=>$name,
-values=>[@p_ids],
-labels=>\%parents,
-default=>$item->category_id,
-multiple=>0,
-size=>1,
);
}
-
mark
