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.

I'm not familiar with Slickgrid but from examples I see it supports numeric sorting if column data has numeric type. So, I believe the only thing you should do here is to change data type of certain data field by calling say parseInt or parseFloat. — triclozan, Mar 21 '14 at 13:38
you're doing it the wrong way, it's the sort that you have to consider... let me find the code for this and I'll put an answer — ghiscoding, Mar 21 '14 at 14:21

score 15 · Accepted Answer · edited Feb 01 '18 at 23:52

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).

Thank you very much! I understand now why I was finding nothing by searching at the wrong place. and yes, Im Québécois ;) — Below the Radar, Mar 21 '14 at 14:55
Can you provide the source of those codes, maybe I would like to sort other formats also. — Below the Radar, Mar 21 '14 at 14:56
Which sort are you looking for? I also have a sort for Date ISO if you want... Sort are simple function, you can google that easily, it's not a SlickGrid thing... — ghiscoding, Mar 21 '14 at 14:59
Yes I was thinking about sorting Date ISO. And by the way, as you seem to be an expert, is there functions to validate data type when a cell is edited that could be implemented in slickgrid? — Below the Radar, Mar 21 '14 at 15:04
I updated my code to have the Date ISO as well, which BTW you simply have to find which one is bigger than the other and return 0 (they are equal), 1 (a > b) or -1 (a < b) and you can find a bunch of sort by googling... for an editor check, yes but that is outside of this question here... you should post another question — ghiscoding, Mar 21 '14 at 15:09
but here anyway, I already answered a question like that, you can give me +1 there too lol... mine is not the accepted one though but here... take a look: http://stackoverflow.com/questions/9180828/slickgrid-validate-column-with-regex — ghiscoding, Mar 21 '14 at 15:14
What version of SlickGrid do you use? sorter parameter in column definition seems to not be in mine — Below the Radar, Mar 21 '14 at 16:30
sorter is not in the [column options list](https://github.com/mleibman/SlickGrid/wiki/Column-Options) — Below the Radar, Mar 21 '14 at 17:00
Ahh I think I forgot a piece of code indeed the `sorter` should be attached as a addon.. let me update my code, sorry I haven't tested it completely, I just copied code from a project... Add and try new code please, see Edit on answer. — ghiscoding, Mar 21 '14 at 18:34

score 0 · Answer 2 · edited Sep 02 '20 at 21:16

I used this to sort the numbers correctly.

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

    data.sort(function(dataRow1, dataRow2) {
        for (var i = 0, l = cols.length; i < l; i++) {
            var result = sortOnString(cols, i, dataRow1, dataRow2);
            if (result != 0) {
                return result;
            }
        }
        return 0;
    });
    grid.invalidate();
    grid.render();
});

function sortOnString(cols, i, dataRow1, dataRow2) {
    var field = cols[i].sortCol.field;
    var sign = cols[i].sortAsc ? 1 : -1;
    console.log("name filed " + field);

    if (field === 'Folio' || field === 'Orden') {
        var value1 = parseInt(dataRow1[field]),
            value2 = parseInt(dataRow2[field]);
        console.log("name value 1 " + value1 + "name value 2 " + value2);
    } else {
        var value1 = dataRow1[field],
            value2 = dataRow2[field];
    }
    var result = (value1 == value2 ? 0 : (value1 > value2 ? 1 : -1)) * sign;
    return result;
}

Slickgrid - Custom column sorters

2 Answers2