KendoUI Grid: custom sorting algorithms


There was a couple of times that I have had to sort columns in a KendoUI Grid but using my custom order. I mean, not using out-of-the-box ordering but doing something especial. Example: order ignoring accents, upper-lower case… but I couldn’t find how to do easily do it and end-up implementing server side filtering and sending the data sorted from the server. For what I was looking for, that was acceptable but this is not always desirable.

This week Atanas Korchev (@korchev) from Telerik, show us how to do it in Stack Overflow: easy and neat! Let me show you how.

KendoUI Grid: defining sortable columns

This is pretty easy, just define sortable: true in your Grid and it will work.

$("#grid").kendoGrid({
    dataSource: {
        data    : [
            { id: 1, name: "one" },
            { id: 2, name: "two" },
            { id: 3, name: "three" },
            { id: 4, name: "four" },
            { id: 5, name: "five" }
        ],
        pageSize: 10,
        schema  : {
            model: {
                fields: {
                    id  : { type: 'number' },
                    name: { type: 'string' }
                }
            }
        }
    },
    editable  : false,
    pageable  : false,
    sortable  : true,
    columns   : [
        { field: "id", title: "Number" },
        { field: "name", title: "Name" }
    ]
});

And you get:

Sortable Grid
Sortable Grid

and if you click on the column header it will sort the column numerically or alphabetically.

KendoUI Grid: Defining custom order

But, what about if you want to order the column by Name but you want that it should be considered as numbers…

If we look at KendoUI documentation about sortable we can define it at Grid level:

Sortable Grid option
Sortable Grid option

and at column level:

KendoUI Grid column sortable option
KendoUI Grid column sortable option

But despite the first says Can be set to a JavaScript object which represents the sorting configuration it does not provide any detail.

At least now, we have some details. Here is how to define a custom compare function.

KendoUI Grid: Custom compare function

Despite the documentation says that columns.sortable needs to be a Boolean it actually can be an object and one of the members of this object is a compare function that return -1 if first argument is less than seconde, 0 if both are the same or +1 if the second is greater that then first.

{
    field   : "name",
    width   : 200,
    title   : "Name",
    sortable: {
        compare: function (a, b) {
            return numbers[a.name] - numbers[b.name];
        }
    }
}

Now, for the sake of this example, what I do is defining an associative array for translating number names into numbers (this is only an example!).

var numbers = {
    "one"  : 1,
    "two"  : 2,
    "three": 3,
    "four" : 4,
    "five" : 5
};

Now, my grid definition is:

var grid = $("#grid").kendoGrid({
    dataSource: {
        data    : [
            { id: 1, name: "one" },
            { id: 2, name: "two" },
            { id: 3, name: "three" },
            { id: 4, name: "four" },
            { id: 5, name: "five" }
        ],
        pageSize: 10,
        schema  : {
            model: {
                fields: {
                    id  : { type: 'number' },
                    name: { type: 'string' }
                }
            }
        }
    },
    editable  : false,
    pageable  : false,
    sortable  : true,
    columns   : [
        { field: "id", title: "Number" },
        {
            field   : "name",
            title   : "Name",
            sortable: {
                compare: function (a, b) {
                    return numbers[a.name] - numbers[b.name];
                }
            }
        }
    ]
}).data("kendoGrid");

And when sorting what I get is:

KendoUI Grid custom sorted
KendoUI Grid custom sorted

KendoUI Grid: custom sorting strings

Have you ever tried sorting a column with string with mixed upper-lower case, accents…? Seems that KendoUI engineers have considered that we want alphabetical order (which is very useful) and not ASCII order.

Let’s consider that we have the following data in our DataSource

[
    { id: 1, name: "Johan" },
    { id: 2, name: "john" },
    { id: 3, name: "Jöhän" },
    { id: 4, name: "john" },
    { id: 5, name: "John" },
    { id: 6, name: "john" }
]

Where we see John in uppercase (record with id 5) and in lowercase (record with id 2, 4 and 6) or Johan with different punctuations.

If we define a Grid with this data and sort it by column name

var grid = $("#grid").kendoGrid({
    dataSource: {
        data    : [
            { id: 1, name: "Johan" },
            { id: 2, name: "john" },
            { id: 3, name: "Jöhään" },
            { id: 4, name: "john" },
            { id: 5, name: "John" },
            { id: 6, name: "john" }
        ],
        pageSize: 10,
        schema  : {
            model: {
                fields: {
                    id  : { type: 'number' },
                    name: { type: 'string' }
                }
            }
        }
    },
    editable  : false,
    pageable  : false,
    sortable  : true,
    columns   : [
        { field: "id", title: "Number" },
        { field: "name", title: "Name" }
    ]
}).data("kendoGrid");

What I get is:

Sorting name with default compare function
Sorting name with default compare function

That show that “Jöhään” is lower than “Johan” and “john” is always lower than “John” (odd?!)

Let’s define a sorting function using standard order. What I will do show side by side two grids with the same data.

My Grid definition is:

var grid1 = $("#grid1").kendoGrid({
    dataSource: {
        data    : [
            { id: 1, name: "Johan" },
            { id: 2, name: "john" },
            { id: 3, name: "Jöhään" },
            { id: 4, name: "john" },
            { id: 5, name: "John" },
            { id: 6, name: "john" }
        ],
        pageSize: 10,
        schema  : {
            model: {
                fields: {
                    id  : { type: 'number' },
                    name: { type: 'string' }
                }
            }
        }
    },
    editable  : false,
    pageable  : false,
    sortable  : true,
    columns   : [
        { field: "id", title: "Number" },
        { field: "name", title: "Name" }
    ]
}).data("kendoGrid");
var grid2 = $("#grid2").kendoGrid({
    dataSource: {
        data    : [
            { id: 1, name: "Johan" },
            { id: 2, name: "john" },
            { id: 3, name: "Jöhään" },
            { id: 4, name: "john" },
            { id: 5, name: "John" },
            { id: 6, name: "john" }
        ],
        pageSize: 10,
        schema  : {
            model: {
                fields: {
                    id  : { type: 'number' },
                    name: { type: 'string' }
                }
            }
        }
    },
    editable  : false,
    pageable  : false,
    sortable  : true,
    columns   : [
        { field: "id", title: "Number" },
        {
            field   : "name",
            title   : "Name",
            sortable: {
                compare: function (a, b) {
                    return a.name === b.name ? 0 : (a.name > b.name) ? 1 : -1;
                }
            }
        }
    ]
}).data("kendoGrid");

You can se that for the first Grid I do not define any “special” sortable option while for second, I defined a compare function that returns 0, 1 or -1.

compare: function (a, b) {
    return a.name2 === b.name2 ? 0 : (a.name2 > b.name2) ? 1 : -1;
}

Now, if I order incrementally by name both Grids, I get:

String column with custom compare function.
String column with custom compare function.

Where now uppercase come before lowercase and non accented character also before than same with accent or punctuation.

Advertisements

8 thoughts on “KendoUI Grid: custom sorting algorithms

    • Everything is possible but as far as I know there is nothing specific for exporting a KendoUI grid to PDF. You should do it from scratch or using some library for doing it.

  1. Why wouldn’t kendo use the “field” property to pass you what a & b is?

    They allow you to set sortable.compare on the root level of the grid properties object, but it’s useless as you don’t know which column the user is sorting on. So now you have to set a sort function for each column, and it’s harder to make a generic sort function. Infuriating!

    • Why? I cannot answer it, did you try asking them? 🙂 Maybe you can send it to the VoC and they can implement it in future releases.

      Setting a generic function doesn’t so difficult. Instead of doing this:

      columns   : [
          {
              field   : "lastname",
              title   : "Last Name",
              sortable: {
                  compare: function (a, b) {
                      return a.lastname === b.lastname ? 0 : (a.lastname > b.lastname) ? 1 : -1;
                  }
              }
          },
          {
              field   : "name",
              title   : "Name",
              sortable: {
                  compare: function (a, b) {
                      return a.name === b.name ? 0 : (a.name > b.name) ? 1 : -1;
                  }
              }
          }
      ]
      

      You can simply do is:

      columns   : [
          {
              field   : "lastname",
              title   : "Last Name",
              sortable: {
                  compare: function (a, b) {
                      genCompare ('lastname', a, b);
                  }
              }
          },
          {
              field   : "name",
              title   : "Name",
              sortable: {
                  compare: function (a, b) {
                      genCompare ('lastname', a, b);
                  }
              }
          }
      ]
      

      And then define genCompare as:

      function getCompare (field, a, b) {
          return a[field] === b[field] ? 0 : (a[field] > b[field]) ? 1 : -1;
      }
      

      I think that this should be enough.

      • I don’t see any event for sortable() property of server wrapper to have this custom handler attached.I have serveroperation false in my grid. Can you please provide a code snippet ?

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s