Forum .LRN Q&A: Better sort order for dotlrn main portlet
just wondering if someone can help with the dotlrn main portlet. At present the main portlet seperates between classes and communities but all the communities or the classes themselves and their subgroups are not sorted alphabetically. I looked at the select_communities query in dotlrn-main-portlet-procs.tcl but of course simply adding a second order key "dotlrn_communities_all.pretty_name" to "order by dotlrn_communities_all.tree_sortkey" doesn't work since first of all all sort keys are different and second this would not sort the classes and communities.
So basically I gave up since I really don't understand how sort key works and what would be neccessary to sort the list in the dotlrn main portlet.
Has someone an idea how to solve the problem or should I simply report a "cosmetic"-type of bug?
The idea is to substitute every level of tree_sortkey by the "pretty name", excepts the first one, that will remain unchanged, with a PL/SQL function, and then, sort by this new "field".
For instance, a tree_sortkey (let's say that every level has only 2 digits) like:
00+02+01+05+... (plus sings only for improve reading)
will be translated into:
00+course about blablabla+subgroup of blablabla+subgroup of a subgroup+...
That way, every course will be under "Courses" item, and every community under "Community" one, and every set of subgroups will be sorted alphabetically below its parent. Let us try it, but I think it sounds reasonably well.
I've done the changes on an Oracle + oacs 5.3 + .LRN 2.3.0. I could try to do as well for PG:
First, I've added a new PL/SQL function at the end of the
dotlrn_community package (file:
function tree_sortkey_2_pretty_name (
p_tree_sortkey in dotlrn_communities_all.tree_sortkey%TYPE
) return varchar2
v_name_sortkey varchar2(4000) default '';
-- make a recursive loop for every level of tree_sortkey, excepts 1st one
if tree.tree_level(p_tree_sortkey) > 1 then
-- get group pretty name from every level
select dotlrn_communities_all.pretty_name into v_name_sortkey
where dotlrn_communities_all.tree_sortkey = p_tree_sortkey;
-- courses has 2 extra levels that don't appear on dotlrn_communities_all
exception when no_data_found then
v_name_sortkey := null;
v_aux := tree.parent_key(p_tree_sortkey);
if v_name_sortkey is null then
-- if there is no pretty name
v_name_sortkey := dotlrn_community.tree_sortkey_2_pretty_name(v_aux);
v_name_sortkey := dotlrn_community.tree_sortkey_2_pretty_name(v_aux) || ' ' || lower(v_name_sortkey);
-- base case
v_name_sortkey := p_tree_sortkey;
and then, adding just this line on
dotlrn_community.tree_sortkey_2_pretty_name(tree_sortkey) as my_orderby
dotlrn-main-portlet is sorted alphabetically!
I'm guessing what file name should have the upgrade.sql file...
PS: I have to thank my workmates for their support. Alberto, Raul, Mario, thanks!
*in fact* the *very reason* we use RAW (Oracle) and bitstring (PG) is because the original char string implementation of tree sortkey failed for some character sets.
Thus the use of a datatype that's not charset dependent.
My idea is to allow the user to choose between these two order options, and give him a button to switch between them (sorted by join date or alphabetically). Maybe, this options could be disabled for strange character sets.
In our production server there are several users with lots of courses and subgroups (hundreds of them in some cases), so it's needed a better way to sort the groups portlet. Maybe, pagination is also needed. I'm thinking about using the "cloud tag" concept in order to highlight some courses, for instance, the recently used ones. But the idea I like the more is to allow the user to mark a course as favorite. Just ideas right now.
Anyway, the actual approach takes the double of time. With "only" 25 items on courses lists (courses and 2 levels of subgroups) and 11 communities, it took 24ms average with "normal" order, and 47ms with alphabetical order. So maybe it's needed a new implementation: pre-calculate the "tree_sortkey_with_prettynames" values in a new column of dotlrn_communities_all, and refresh it triggered by any "course name" update.