How to do natural sort/alpha numeric sort over columns for y-dimension in jqGrid pivot table

Question

For columns in y-dimension how to do natural sort for alpha numeric column names ?

For example: consider column names AA1, AA2, AA3, AA10, AA11. These are listed in order AA1, AA10, AA11, AA2, AA3 in pivot table y-dimension.

Desired order of columns is AA1, AA2, AA3, AA10, AA11

Answer 1

Free jqGrid 4.9 contains full rewritten version of jqPivot. I tried to hold compatibility with the previous version, but it contains many advanced features. I tried to describe there in wiki.

Not so many people uses jqPivot. So I remind what it do. It gets an input data as source and generate new data, which will be input data for jqGrid. Additionally jqPivot generates colModel based on input data and yDimension parameter. During analyzing of input data jqPivot sorts input data by xDimension and by yDimension. The order or sorting of xDimension defines the order of rows of resulting grid. The order or sorting of yDimension defines the order of columns of resulting grid and the total number of resulting columns. The options compareVectorsByX and compareVectorsByY of allows to specify callback function which will be used for custom sorting by the whole x or y vector. It's important to understand that sorting function not only specify the the order of columns, but it informs jqPivot which vectors should be interpreted as the same. For example it can interpret the values 12, 12.0 and 012.00 as the same and specify that 12.0 is larger as 6.

I describe below some ways which can be used to customize sorting by xDimension and yDimension.

First of all one can specify skipSortByX: true or skipSortByY: true parameters. In the case the input data have to be already sorted in the order which you want. The next important options are Boolean options caseSensitive (with default value false) and trimByCollect (default value true). caseSensitive: true can be used to distinguish input data by case and trimByCollect: false can be used to hold trailing spaces in the input data.

Some other important option can be specified in xDimension or yDimension: sorttype and sortorder. sortorder: "desc" can be used to reverse the order of sorted data. The option sorttype can be "integer" (or "int") which means to truncate (Math.floor(Number(inputValue))) input data during sorting; The values "number", "currency" and "float" means that the input data should be converted to numbers during sorting (Number(inputValue)). Finally one can don't specify any sorttype, but specify compare callback function instead. The compare callback is function with two parameters and it should return well known -1, 0 or 1 values.

For example I created the demo for one issue. One asked my about the following situation. The web site contains login, which identifies the country of the user. One want to set the user's country as the first in the sorting order. The demo uses the following yDimension parameter:

yDimension: [
    { dataName: "sellyear", sorttype: "integer" },
    { dataName: "sell month",
        compare: function (a, b) {
            if (a === "Germany") { return b !== "Germany" ? -1 : 0; }
            if (b === "Germany") { return 1; }
            if (a > b) { return 1; }
            if (a < b) { return -1; }
            return 0;
        }}
]

It sets "Germany" first in the sorting order. As the results one sees the results like on the picture below

You can use the same approach using the code for natural compare from the answer and you will implements your requirements.

In more advanced cases one can use the options compareVectorsByX and compareVectorsByY. The requirement was to place specific country only in one specific year on the first place holding the standard order on all other cases. The corresponding demo uses compareVectorsByY to implement the requirement. It displays

and uses the following compareVectorsByY:

compareVectorsByY: function (vector1, vector2) {
    var fieldLength = this.fieldLength, iField, compareResult;

    if (fieldLength === 2) {
        if (vector1[0] === "2011" && vector1[1] === "Germany") {
            if (vector2[0] === "2011" && vector2[1] === "Germany") {
                return {
                    index: -1,
                    result: 0
                };
            }
            return {
                index: vector2[0] === "2011" ? 1 : 0,
                result: -1
            };
        }
        // any vector1 is larger as vector2 ("2011", "Germany")
        if (vector2[0] === "2011" && vector2[1] === "Germany") {
            return {
                index: vector2[0] === "2011" ? 1 : 0,
                result: 1
            };
        }
    }

    for (iField = 0; iField < fieldLength; iField++) {
        compareResult = this.fieldCompare[iField](vector1[iField], vector2[iField]);
        if (compareResult !== 0) {
            return {
                index: iField,
                result: compareResult
            };
        }
    }
    return {
        index: -1,
        result: 0
    };
}

It's important to mention that compareVectorsByY callback function should return object with two properties: index and result. The value of result property should be -1, 0 or 1. The value of index property should be -1 in case of result: 0 and be 0-based index of vectors where vector1 and vector2 are different.