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

3: Re: Better sort order for dotlrn main portlet (response to 1)

Posted by Michael Cordova on 07/17/08 01:23 AM

Done!

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!

4: Re: Better sort order for dotlrn main portlet (response to 3)

Posted by Don Baccus on 07/17/08 09:30 AM

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.

5: Re: Better sort order for dotlrn main portlet (response to 4)

Posted by Michael Cordova on 07/17/08 12:45 PM

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.