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

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.