How to stop auto sorting of Jqpivot?

Question

the following jqpivot grid displays sales information of car rentals. The complete code for this is in jsfiddle

var data = [{
    "id": 1,
    "make": "toyota",
    "model": "corolla",
    "fuelusagecity": "17",
    "fuelusagehwy": "12",
    "fuelmeasure":'Litre',
    "salesaboveavg": false,
    "totalnumberofsales": 120000.0000,
    "highsalestext": null,
    "salesdate": "2010-12-01"
}, {
    "id": 2,
    "make": "toyota",
    "model": "corolla",
    "fuelusagecity": "10",
    "fuelusagehwy": "14",
    "salesaboveavg": false,
    "fuelmeasure":'Litre',
    "totalnumberofsales": 100000.0000,
    "highsalestext": "HIGH",
    "salesdate": "2010-12-15"
}, {
    "id": 3,
    "make": "toyota",
    "model": "belta",
    "fuelusagecity": "15",
    "fuelusagehwy": "10",
    "salesaboveavg": true,
    "fuelmeasure":'Litre',
    "totalnumberofsales": 200000.0000,
    "highsalestext": null,
    "salesdate": "2011-01-10"
}, {
    "id": 4,
    "make": "toyota",
    "model": "camry",
    "fuelusagecity": "13",
    "fuelusagehwy": "10",
    "fuelmeasure":'Litre',
    "salesaboveavg": false,
    "totalnumberofsales": 300000.0000,
    "highsalestext": "HIGH",
    "salesdate": "2011-04-23"
}, {
    "id": 5,
    "make": "nissan",
    "model": "skyline",
    "fuelusagecity": "14",
    "fuelusagehwy": "9",
    "fuelmeasure":'Litre',
    "salesaboveavg": true,
    "totalnumberofsales": 500000.0000,
    "highsalestext": "HIGH",
    "salesdate": "2010-09-10"
}, {
    "id": 6,
    "make": "nissan",
    "model": "zx300",
    "fuelusagecity": "10",
    "fuelusagehwy": "8",
    "fuelmeasure":'Litre',
    "salesaboveavg": false,
    "totalnumberofsales": 400000.0000,
    "highsalestext": null,
    "salesdate": "2012-01-06"
}];

/* convert the salesdate in  */
var i, item, dateParts;
for (i = 0; i < data.length; i++) {
    item = data[i];
    if (typeof item.salesdate === "string") {
        dateParts = item.salesdate.split("-");
        item.salesYear = dateParts[0];
        item.salesMonth = dateParts[1];
        item.salesDay = dateParts[2];
        item.salesDateFormatted = dateParts[0];
    }
}

var myIntTemplate = {
    formatter: "currency",
    align: "right", sorttype: "number",
    searchoptions: { sopt: ["eq", "ne", "lt", "le", "gt", "ge"] },
    formatoptions: { defaultValue: ""}
},
    $grid = $("#list483");

$grid.jqGrid("jqPivot",
             data,
             {
    frozenStaticCols: true,
    skipSortByX: true,
    useColSpanStyle: true,
    //defaultFormatting: false,
    xDimension: [
        { dataName: "make", width: 100, label: "Make", compareVectorsEx(x1,x2){
        // how do i use x1, x2 parameters to stop auto sort
        }  },
        { dataName: "model", width: 100, label: "Model", align: "center", skipGrouping:true, compareVectorsEx(x1,x2){

        } },
        { dataName: "fuelmeasure", width: 103, label: "Units", compareVectorsEx(x1,x2){

        } },
    ],
    yDimension: [
        { dataName: "salesdate", sortorder: "desc"}//,
        //{ dataName: "salesYear", sorttype: "integer" },
        //{ dataName: "salesMonth", sorttype: "integer" }
    ],
    aggregates: [{
        member: "totalnumberofsales",
        template: myIntTemplate,
        formatter:function(cellvalue, options, rowObject){
            if(cellvalue=== undefined){
                return '';
            }
            else{

                var x = options.rowData.pivotInfos[options.colModel.name].rows[0].highsalestext;

                if(x==="HIGH")
                {
                    return x;
                }
                else
                {
                    return cellvalue;
                }
            }
        },
        cellattr: function (rowId, cellValue, rawObject, cm, rdata) {
            if (rawObject != null) {
                var items = rawObject.pivotInfos[cm.name];
                if (items != null && items.rows != null && items.rows.length > 0) {
                    var isHigh = true, i;
                    for (i = 0; i < items.rows.length; i++) {
                        if (items.rows[i].highsalestext !== "HIGH") {
                            isHigh = false;
                            break;
                        }
                    }
                    if (isHigh) {
                        return "class='high-marker'";
                    }
                }
            }
        },
        aggregator: "max"
    }/*,
                 {
                 member: "totalnumberofsales",
                 aggregator: "count",
                 //template: "integer",
                 label: "{0}"
                 }*/]
},
             // grid options
             {
    iconSet: "fontAwesome",
    cmTemplate: { autoResizable: true, width: 75 },
    shrinkToFit: false,
    useUnformattedDataForCellAttr: false,
    autoResizing: { compact: true },
    groupingView: {
        groupField: ["x0"],
        groupColumnShow: [false],
        groupText: ["<span class='group-text'>{0}</span>"]
    },
    //width: 450,
    pager: true,
    rowNum: 20,
    caption: "<b>Car sales statistics</b>",
    rowList: [5, 10, 20, 100, "10000:All"]
}
            );

according to this wiki suppress auto sort on jqpivot official wiki at the bottom of the wiki it states:

Custom sorting by the whole x or y vector

The options compareVectorsByX and compareVectorsByY allows to specify callback function which will be used for custom sorting by the whole x or y vector.

The default implementation of sorting by the vector can be found here. It's compareVectorsEx method of ArrayOfFieldsets. It's important to understand that the function will be used for two purpose: 1) compare the vectors 2) finding the index of the vectors where there are differences in the compared vectors. So the method compareVectorsEx returns object with two properties: index and result. The property result is well known value -1, which means that the first vector is less then the second one, 0, means the vectors are equal to, 1, which means that the first vector is greater then the second one. The property index returns 0-based index of the element of the compared vectors where the vectors are different.

i have added the function compareVectorsEx as stated but how do use that function to stop the auto sort?

i have to stop the auto sort of all x fields. The reason i need to stop sort is to make the grid show the fields make and model in the same order as in the original json.

UPDATE:

i have modified the original json datasource to have two properties on each object groupheaderorder and childorder. The property groupheaderorder is the order of the property make of an object in json and childorder property is the order of the model property for all make names.

here is the json data

var data = [{
    "id": 1,
    "make": "toyota",
    "model": "corolla",
    "fuelusagecity": "17",
    "fuelusagehwy": "12",
    "fuelmeasure":'Litre',
    "salesaboveavg": false,
    "totalnumberofsales": 120000.0000,
    "highsalestext": null,
    "salesdate": "2010-12-01", 
    "groupheaderorder":"1",
    "childorder":"1"
}, {
    "id": 2,
    "make": "toyota",
    "model": "corolla",
    "fuelusagecity": "10",
    "fuelusagehwy": "14",
    "salesaboveavg": false,
    "fuelmeasure":'Litre',
    "totalnumberofsales": 100000.0000,
    "highsalestext": "HIGH",
    "salesdate": "2010-12-15",
    "groupheaderorder":"1",
    "childorder":"1"
}, {
    "id": 3,
    "make": "toyota",
    "model": "belta",
    "fuelusagecity": "15",
    "fuelusagehwy": "10",
    "salesaboveavg": true,
    "fuelmeasure":'Litre',
    "totalnumberofsales": 200000.0000,
    "highsalestext": null,
    "salesdate": "2011-01-10",
    "groupheaderorder":"1",
    "childorder":"2"
}, {
    "id": 4,
    "make": "toyota",
    "model": "camry",
    "fuelusagecity": "13",
    "fuelusagehwy": "10",
    "fuelmeasure":'Litre',
    "salesaboveavg": false,
    "totalnumberofsales": 300000.0000,
    "highsalestext": "HIGH",
    "salesdate": "2011-04-23",
    "groupheaderorder":"1",
    "childorder":"3"
}, {
    "id": 5,
    "make": "nissan",
    "model": "skyline",
    "fuelusagecity": "14",
    "fuelusagehwy": "9",
    "fuelmeasure":'Litre',
    "salesaboveavg": true,
    "totalnumberofsales": 500000.0000,
    "highsalestext": "HIGH",
    "salesdate": "2010-09-10",
    "groupheaderorder":"2",
    "childorder":"1"
}, {
    "id": 6,
    "make": "nissan",
    "model": "zx300",
    "fuelusagecity": "10",
    "fuelusagehwy": "8",
    "fuelmeasure":'Litre',
    "salesaboveavg": false,
    "totalnumberofsales": 400000.0000,
    "highsalestext": null,
    "salesdate": "2012-01-06",
    "groupheaderorder":"2",
    "childorder":"2"
}];

Here is link to the jsfiddle code (this is the same code as my original post except the new two properties were added)

let me explain this through an example

In the josn there are three toyotas and two nissans. if you look at groupheaderorder number and childorder number for the same make and model they have the same values but for different makes and models they have different values.So the combination of groupheaderorder and childheaderorder is always unique for the different combinations of makes and models. I thought this could be a good option to give a custom sort order for the grouptext and their children, because they keep and show their original sort order.

The original data comes from a stored procedure that i cannot modify in the stored procedure code and dauntingly the returning result set does not even have the columns that were used to sort. So in my web app that was built from ASP MVC, in its controller logic the only option i have is to add those two sort properties so they will be included in the json.

There could be other better ways and if so i really love to know abouts : )
However since i have added those two properties to preserve the original sort order is there away to keep and show this order in the generated pivot grid?

You use already `skipSortByX: true` option. It means that jqPivot *makes already no sorting of input data*. Nevertheless you have *another problem*. jqPivot aggregate the source data and generate new one, which will by used by the second step: displaying new data in the grid. You use `groupField: ["x0"]`, thus jqGrid **sorts** the previously generated data by `x0` (by `make`). You don't specify any `sorttype` for the column, thus the data will be sorted in asc text order ("nissan" before "toyota"). During the sorting by x0 the second column x1 (model) will be mixed too. — Oleg, Jan 04 '17 at 16:47

Oleg · Accepted Answer · 2017-01-06T08:31:24.083

One should make some small changes in your code. First of all one should remove skipSortByX: true option, which one not need more. Seconds we should extend xVector with groupheaderorder and childorder properties, which we need for sorting. We will don't display the columns, thus we can use additionalProperty: true property for the columns. As the result we can use the following xDimension:

xDimension: [
    { /*x0*/ dataName: "make", width: 100, label: "Make" },
    { /*x1*/ dataName: "model", width: 100, label: "Model", align: "center", skipGrouping:true },
    { /*x2*/ dataName: "fuelmeasure", width: 103, label: "Units", skipGrouping:true },
    { /*x3*/ dataName: "groupheaderorder", additionalProperty: true },
    { /*x4*/ dataName: "childorder", additionalProperty: true }
]

I included comments, which describes the name (the index) of the property: the make property will have the index 0 in the x-vector, the model property will have the index 1, ... the childorder property will have the index 4.

The final jqGrid will have input data with properties x0, x1, ..., x4, which corresponds the columns. To make the columns "make" and "model" (x0, x1) sortable based on groupheaderorder and childorder we can add sorttype callback to two first columns. As the result we get

xDimension: [
    { /*x0*/ dataName: "make", width: 100, label: "Make",
        sorttype: function (cellValue, item) {
            return parseInt(item.x3, 10);
        } },
    { /*x1*/ dataName: "model", width: 100, label: "Model", align: "center", skipGrouping:true,
        sorttype: function (cellValue, item) {
            return parseInt(item.x4, 10);
        }},
    { /*x2*/ dataName: "fuelmeasure", width: 103, label: "Units", skipGrouping:true },
    { /*x3*/ dataName: "groupheaderorder", additionalProperty: true },
    { /*x4*/ dataName: "childorder", additionalProperty: true }
]

Finally we need to implement compareVectorsByX callback, which can be used to sort the data by X-vector. The callback compareVectorsByX have two parameters which will be the x-items from the source data, for example x1 = ["toyota", "corolla", "Litre", "1", "1"] and 2 = ["toyota", "belta", "Litre", "1", "2"]. The callback should return object with two properties: index and result. The result is -1, 0 or 1, which everybody know in comparing of numbers. The index is the index in the x-array where one found the differences between x1 and x2, which one compares. In case of above example one find the difference in the second element of x array (the second element has the index 1). We compare "corolla" and "belta" based on the last ("childorder") value: by comparing "1" and "2". Thus the compareVectorsByX callback should return { index: 1, result: -1 } on the above options. The common code will be like the following:

compareVectorsByX: function (x1, x2) {
    // x1 = ["toyota", "corolla", "Litre", "1", "1"],
    // x2 = ["toyota", "belta", "Litre", "1", "2"]
    var groupheaderorder1 = parseInt(x1[3], 10), childorder1 = parseInt(x1[4], 10),
        groupheaderorder2 = parseInt(x2[3], 10), childorder2 = parseInt(x2[4], 10);
    if (groupheaderorder1 !== groupheaderorder2) {
        return { index: 0, result: groupheaderorder1 - groupheaderorder2 };
    }
    if (childorder1 !== childorder2) {
        return { index: 1, result: childorder1 - childorder2 };
    }
    return {
        index: -1,
        result: 0
    };
}

It's all. The modified demo with the code is https://jsfiddle.net/OlegKi/cukLs23n/2/

thanks for the comprehensive reply : ) . When i compare the orginal jsfiddle that i have posted https://jsfiddle.net/9xxzsuu4/3/ and sorted fiddle with the sorted order https://jsfiddle.net/cukLs23n/1/ shows the same record order. but by adding an explicit sort order the newly generated grid should show data in the same order as they appear in teh json. eg: TOYOTA group must appear before NISSAN and the cars order in TOYOTA group must be in the following order corolla, belta, camry. In the NISSAN group the cars must show in the following order skyline,zx300, Is order numbering wrong? — Dore.Ad, Jan 06 '17 at 00:44
@Dore.Ad: Sorry, I posted just wrong URL at the end of my answer. If you would follow my answer, comment `skipSortByX: true`, replace `xDimension` and add `compareVectorsByX` then you would have the correct sorting results like on https://jsfiddle.net/OlegKi/cukLs23n/2/ — Oleg, Jan 06 '17 at 08:33
i wondered what the issue was. now the custom sort order started to take effect. thanks heaps! : ) — Dore.Ad, Jan 06 '17 at 14:14
@Dore.Ad: I just posted URL with wrong demo (https://jsfiddle.net/cukLs23n/1/), which didn't use all, what I described in my answer. The demo https://jsfiddle.net/OlegKi/cukLs23n/2/ uses `compareVectorsByX` and so on and it works. — Oleg, Jan 06 '17 at 14:16

How to stop auto sorting of Jqpivot?

1 Answers1

Linked