Ask Reuben

Sort Comparison Function

How can I change the sort order used when I click on a column header?

How can I change sort order used with the array.sort method? 

How can I sort like iTunes and exclude ‘The’ from the beginning of a word for sort comparison purposes?

How can I sort like Explorer where numbers may be simplified i.e. 1000 is 1K ?

How can I sort character data numerically i.e. 1,2,10, not 1,10,2?

When you look around other applications a question you should ask yourself is how would I implement that in a Genero application?

One such question arises from programs like Windows Explorer, Mac OSX Finder.  If you note the file size column, the size is simplified so that instead of saying 2000 or 1000000, it might say 2K, 1M.  When you click on the column header, it still sorts correctly, that is 2K < 1M You may also see something similar in the app you use for your music and movies.  Is "The Beatles" sorted as if it begins with T or B?  Is "The Lord of the Rings" sorted as if it began with T or L? In a recent Genero maintenance release, functionality was added so that you can control the sort comparison function used. This is in the form of two new methods.

  • For dynamic arrays you have the array.sortByComparisonFunction method.  This will sort the array using the indicated sort comparison function.
  • For use in DISPLAY ARRAYS, you have the ui.Dialog.setColumnComparisonFunction method.  This does not perform the sort straight away, it indicates what sort comparison function to use if the user clicks on that columns column header.

Both of these methods use the concept of function references to allow you to pass the name of a function to a method.  This function is then used to compare two values from the array and return a value that indicates the relationship between the two functions.

The sort comparison function must have the following signature FUNCTION (s1 STRING, s2 STRING) RETURNS INTEGER and it is expected to return a negative value to indicate that s1 should appear before s2, a positive value to indicate that s1 should appear after s2.

Examples of a sort comparison function might be

-- A sort that ignores The if the sort column begins The ...
FUNCTION itunes_sort(s1 STRING, s2 STRING) RETURNS INTEGER

    IF s1.subString(1, 4) = "The " THEN
        LET s1 = s1.subString(5, s1.getLength())
    END IF
    IF s2.subString(1, 4) = "The " THEN
        LET s2 = s2.subString(5, s2.getLength())
    END IF
    RETURN util.Strings.collate(s1,s2)
END FUNCTION
-- Sort based on length of sort value
FUNCTION length_sort(s1 STRING, s2 STRING) RETURNS INTEGER

    RETURN s1.getLength() - s2.getLength()
END FUNCTION

Some things to note.

  • There are two methods that have been added to the util.Strings class.  These can be used as part of your function or be passed  as the sort comparison argument.
    • util.Strings.collate is a sort comparison function that can be used for character data
    • util.Strings.collateNumeric is a sort comparison function that can be used for numeric data in a character field.  When you need 1<2<100 and not 1<100<2
  • The function does not have to return -1, 0, 1.  It can be any negative or positive value, so don’t waste CPU values trying to create -1,0,1, as long as result is negative or positive or 0 is all that matters.
  • Consider that the function will be called multiple times.  If you note the example here that converts a clothing size (XXS, XS, S, M, L, XL, XXL) to an integer (-3,-2, -1,0,1,2,3), the dictionary for the loopkup function size2int() is defined once at the modular level.  If it was local to the size2int function than each time the function was called that space would be allocated and the dictionary initialised.
  • Don’t forget to consider the case where a value might be NULL.  Where do you want this item to appear in the sort results.
  • The ui.Dialog.setColumnComparisonFunction does not sort the data straight away, only when the user clicks on the column header.  You may also need an explicit array.sortByComparisionFunction particularly if you have an ON ACTION that changes the sort method and displays the sort method used.
  • Using array.sortByComparisionFunction in a dialog, you would need to use UNBUFFERED to see result straight away.

Demo programs were added to $FGLDIR/demo/Sorting and can be found in the Demo program under New to 4.01 -> Enhanced Sorting.  The screenshot below is from one of those demo programs and compares the sorting of clothing sizes, 3XS, XXS, XS, M, L, XL, XXL, 3XL.  The default sort is alphabetical, whilst the user sort uses a sort comparison function to achieve the desired sort order.