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?

Greetings,
Nima

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
    is
        v_name_sortkey varchar2(4000) default '';
        v_aux dotlrn_communities_all.tree_sortkey%TYPE;
    begin
        -- 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
            begin
                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;
            end;
            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);
            else
                v_name_sortkey := dotlrn_community.tree_sortkey_2_pretty_name(v_aux) || '   ' || lower(v_name_sortkey);
            end if;
        else
            -- 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
my_orderby
--dotlrn_communities_all.tree_sortkey


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!

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.