Forum .LRN Q&A: Better sort order for dotlrn main portlet

Request notifications

Hi all,

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?


Posted by Miguel Cordova on
Today, Alberto Pesquera and Me have reached to an approach, I think it could be ok.

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.

Posted by Miguel Cordova on

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: dotlrn/sql/oracle/communities-package-create.sql)

function tree_sortkey_2_pretty_name (
p_tree_sortkey in dotlrn_communities_all.tree_sortkey%TYPE
) return varchar2
v_name_sortkey varchar2(4000) default '';
v_aux dotlrn_communities_all.tree_sortkey%TYPE;
-- 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
from dotlrn_communities_all
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);
end if;
-- base case
v_name_sortkey := p_tree_sortkey;
end if;

return v_name_sortkey;

end tree_sortkey_2_pretty_name;

and then, adding just this line on dotlrn-main-portlet-oracle.xql:

select dotlrn_communities_all.*,
dotlrn_community.tree_sortkey_2_pretty_name(tree_sortkey) as my_orderby
from dotlrn_communities_all,
order by

The 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!

Posted by Don Baccus on
I don't feel very confident about this. Among other things, the implicit conversion from RAW to VARCHAR2 might lead to a different sort order in strange character sets. We can't just convert a bit string to a char string and add on another character string in PG and get reliable results that work across charsets, that I know for sure.

*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.

Posted by Miguel Cordova on
About the conversion from RAW to VARCHAR2, is there a safer way? I've tried using spanish characters and everything seems to work well.

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.