Slickgrid - Custom column sorters

Question

I would like to know if it's possible to change the data type for a column. For instance, the json data passed to the grid are strings, but I would like slickgrid to consider it as integers or floats to be able to sort it correctly.

var data = [{"NOM": "Saguenay - Lac-Saint-Jean", "CODE": "02", "id": "0", "integer": "1"},]

I would like the 'integer' column to be an int not a string, without changing the data itself.

Thank you for your help.

Answer 1

As I mentioned in my comment, you are looking at the wrong place (no offense); there is no need to change datatype as actually this will not fix your problem with sort, since the SlickGrid default sort is string sort. But you could use custom sort to fix your problem.

So here is the solution: Define sort function and use them as needed. Here is a list of custom sort functions you could create:

function sorterStringCompare(a, b) {
    var x = a[sortcol], y = b[sortcol];
    return sortdir * (x === y ? 0 : (x > y ? 1 : -1));
}
function sorterNumeric(a, b) {
    var x = (isNaN(a[sortcol]) || a[sortcol] === "" || a[sortcol] === null) ? -99e+10 : parseFloat(a[sortcol]);
    var y = (isNaN(b[sortcol]) || b[sortcol] === "" || b[sortcol] === null) ? -99e+10 : parseFloat(b[sortcol]);
    return sortdir * (x === y ? 0 : (x > y ? 1 : -1));
}
function sorterRating(a, b) {
    var xrow = a[sortcol], yrow = b[sortcol];
    var x = xrow[3], y = yrow[3];
    return sortdir * (x === y ? 0 : (x > y ? 1 : -1));
}
function sorterDateIso(a, b) {
    var regex_a = new RegExp("^((19[1-9][1-9])|([2][01][0-9]))\\d-([0]\\d|[1][0-2])-([0-2]\\d|[3][0-1])(\\s([0]\\d|[1][0-2])(\\:[0-5]\\d){1,2}(\\:[0-5]\\d){1,2})?$", "gi");
    var regex_b = new RegExp("^((19[1-9][1-9])|([2][01][0-9]))\\d-([0]\\d|[1][0-2])-([0-2]\\d|[3][0-1])(\\s([0]\\d|[1][0-2])(\\:[0-5]\\d){1,2}(\\:[0-5]\\d){1,2})?$", "gi");

    if (regex_a.test(a[sortcol]) && regex_b.test(b[sortcol])) {
        var date_a = new Date(a[sortcol]);
        var date_b = new Date(b[sortcol]);
        var diff = date_a.getTime() - date_b.getTime();
        return sortdir * (diff === 0 ? 0 : (date_a > date_b ? 1 : -1));
    }
    else {
        var x = a[sortcol], y = b[sortcol];
        return sortdir * (x === y ? 0 : (x > y ? 1 : -1));
    }
}

and then in your columns definition you would use whichever custom filter you need, in your case the sorterNumeric() is what you're looking for...so your columns definition would look like the following (custom filter are at the end):

var columns = [                    
    {id:"column1", name:"column1", field: "Column String", width:40, sortable:true, sorter:sorterStringCompare},
    {id:"column2", name:"column2", field: "Column integer", width:40, sortable:true, sorter:sorterNumeric},
    {id:"column3", name:"column3", field: "Column rating", width:40, sortable:true, sorter:sorterRating}    
];

Saguenay...? Quebecois? :)

EDIT
I forgot to add the piece of code that attach the new sorter property to the onSort event (of course without it then it won't work), make sure you have same object name for grid and dataView, correct to whatever your variables naming are (if need be), here is the code:

grid.onSort.subscribe(function (e, args) {
  var cols = args.sortCols;

  dataView.sort(function (dataRow1, dataRow2) {
  for (var i = 0, l = cols.length; i < l; i++) {
      sortdir = cols[i].sortAsc ? 1 : -1;
      sortcol = cols[i].sortCol.field;

      var result = cols[i].sortCol.sorter(dataRow1, dataRow2); // sorter property from column definition comes in play here
      if (result != 0) {
        return result;
      }
    }
    return 0;
  });
  args.grid.invalidateAllRows();
  args.grid.render();
});

You could also put your code directly into the last onSort.subscribe but I suggest having the sorter into a separate function since it is cleaner (which is the code I sent).