Here's a hack that gives me correctly sorted and ordered links (and works in other modules too):
create function alpha_category_hierarchy_sortkey(integer, integer, char)
returns char as '
DECLARE
v_category_id alias for $1;
v_start_id alias for $2;
v_sortkey alias for $3;
v_parent_id integer;
v_parent_name varchar(40);
v_category_name varchar(40);
BEGIN
select parent_category_id into v_parent_id from category_hierarchy where child_category_id= v_category_id;
select category into v_parent_name from categories, category_hierarchy where child_category_id = v_category_id AND c
ategory_id = v_parent_id;
select category into v_category_name from categories, category_hierarchy where category_id = v_category_id;
IF v_category_id = v_start_id
then return (v_category_name::char || v_sortkey);
end if;
if v_parent_id is null
then return NULL;
end if;
return alpha_category_hierarchy_sortkey(v_parent_id, v_start_id, v_category_name::char || v_sortkey) ;
END;
' language 'plpgsql';
I'm calling this function with code like:
SELECT c.category_id, c.category, c.category_type, url, link_title, n_ratings, round(avg_rating, 1) as avg_rating,
category_hierarchy_level(c.category_id, $start_with_proc, 0) - 1 as indent,
gl.link_id
FROM categories c, category_hierarchy h, category_hierarchy h1, general_links gl, site_wide_category_map swm
WHERE c.category_id= h.child_category_id
AND category_hierarchy_level(h.child_category_id, h1.child_category_id, 1) is not null
AND gl.link_id = swm.on_what_id
AND swm.category_id = c.category_id
$where_clause_for_search_query
$where_clause_for_section
ORDER BY alpha_category_hierarchy_sortkey(h.child_category_id, h1.child_category_id, ''), link_title
