Discussing Natural Sorting Using jqGrid and ASP.Net

Contents

  • Abstract
  • Introduction
  • What Natural Sorting is 
  • Discussing problems
  • The birth of a new feature in jqGrid
  • Implementation

    • jqGrid code
    • Data for sorting
    • Data after string sorting
    • Data after Natural Sorting

  • What next
  • Closing Notes
Abstract

This entire article is discussing an actual problem and all about how the new feature "Natural Sorting" is requested and added to jqGrid. As a case study we need to implement Natural Sorting using JqGrid with ASP.NET. Through-out this project we will see how to implement it in JqGrid.

Introduction

In the current version of JqGrid we have the ability to sort our columns in a natural way. Earlier versions of JqGrid did not have that capability. In this article, we will see how this feature has been implemented in JqGrid and how to use it.

In future sections we will see how the request has been made for this new feature and how Natural Sorting works with jqGrid.

What Natural Soring is

Before going into the details of the implementation, let's first understand the term Natural Sorting. So, what is Natural Sorting?

In simple words we can say, it is nothing but a kind of alphanumeric sorting in a natural way. Let's understand this with the following example.
 
We have the following data that needs to be sorted: 
 
  1. //Needs to sort    
  2. 1 3 10 11 1A AA AB 2 A1  
 
Let's sort it into alphanumeric sort order as in the following:
 
  1. //Alphanumeric sorting also known as string sorting  
  2. 1 10 1A 11 2 3 AA A1 AB  

See the difference? In the data, if we sort in NaturalSort order as in the following:
 
  1. //Natural sorting  
  2. 1 1A 2 3 10 11 A1 AA AB  
 
So, Natural Sorting is something that seems natural to humans, we know 2 should be placed before 11 and after 1, that is possible using NaturalSort order or Natural Sorting.


Discussing the problem

Now to return to the original problem. :) I was saying, it's not possible sort our data in NaturalSort order using jqGrid before its current release version 4.6.0.

I was the main lead for the project and need to solve this issue any how and the challenge is we were not allowed to replace a jqGrid with other jQuery plugins that have a builtin NaturalSort algorithm.

I started research and found some interesting things, I found:

  1. Natural Sorting algorithm in JavaScript.
  2. Unit Tests

The preceding is only the solution for our issue, I was very excited and started the implementation, I used a custom formula with jqGrid and unfortunately it was not supported.

The birth of a new feature in jqGrid


I posted the issue in many forms and publishing/article places, including stack overflow.

Unfortunately, I did not get a solution for this issue. And then I approached Tony Move, the creator of jqGrid. Afterwards, I logged a bug for this issue at here: NaturalSort.

I do not want to to feed my own custom function into jqGrid source file(s). I can't do that; the reason is simple, it will be automatically washed-out.

When someone upgrades the jqGrid version. We have only option to wait and see when this issue will be fixed and is available with the new release of jqGrid.

Finally, on February 08, 2014, Tony Move announced the new feature here. It will make my life easier, I was very happy and waiting for the new release of jqGrid.

Implementation

This new feature has the ability to add our custom sort function; in my case I need this:

  1. //3 parmeters comapre values a,b and direction 1 for ascending -1 for descending  
To make this working, Tony Move made some corrections to the existing JavaScript Natural Sorting function as:
  1. /* 
  2.  * Natural Sort algorithm for JavaScript - Version 0.7 - Released under MIT license 
  3.  * Author: Jim Palmer (based on chunking idea from Dave Koelle) 
  4.  */  
  5.  function naturalSort (a, b, d) {  
  6.     if(d===undefined) { d=1; }  
  7.     var re = /(^-?[0-9]+(\.?[0-9]*)[df]?e?[0-9]?$|^0x[0-9a-f]+$|[0-9]+)/gi,  
  8.         sre = /(^[ ]*|[ ]*$)/g,  
  9.         dre = /(^([\w ]+,?[\w ]+)?[\w ]+,?[\w ]+\d+:\d+(:\d+)?[\w ]?|^\d{1,4}[\/\-]\d{1,4}[\/\-]\d{1,4}|^\w+, \w+ \d+, \d{4})/,  
  10.         hre = /^0x[0-9a-f]+$/i,  
  11.         ore = /^0/,  
  12.         i = function(s) { return naturalSort.insensitive && (''+s).toLowerCase() || ''+s },  
  13.         // convert all to strings strip whitespace  
  14.         x = i(a).replace(sre, '') || '',  
  15.         y = i(b).replace(sre, '') || '',  
  16.         // chunk/tokenize  
  17.         xN = x.replace(re, '\0$1\0').replace(/\0$/,'').replace(/^\0/,'').split('\0'),  
  18.         yN = y.replace(re, '\0$1\0').replace(/\0$/,'').replace(/^\0/,'').split('\0'),  
  19.         // numeric, hex or date detection  
  20.         xD = parseInt(x.match(hre)) || (xN.length != 1 && x.match(dre) && Date.parse(x)),  
  21.         yD = parseInt(y.match(hre)) || xD && y.match(dre) && Date.parse(y) || null,  
  22.         oFxNcL, oFyNcL;  
  23.     // first try and sort Hex codes or Dates  
  24.     if (yD)  
  25.         if ( xD < yD ) return -d;  
  26.         else if ( xD > yD ) return d;  
  27.     // natural sorting through split numeric strings and default strings  
  28.     for(var cLoc=0, numS=Math.max(xN.length, yN.length); cLoc < numS; cLoc++) {  
  29.         // find floats not starting with '0', string or 0 if not defined (Clint Priest)  
  30.         oFxNcL = !(xN[cLoc] || '').match(ore) && parseFloat(xN[cLoc]) || xN[cLoc] || 0;  
  31.         oFyNcL = !(yN[cLoc] || '').match(ore) && parseFloat(yN[cLoc]) || yN[cLoc] || 0;  
  32.         // handle numeric vs string comparison - number < string - (Kyle Adams)  
  33.         if (isNaN(oFxNcL) !== isNaN(oFyNcL)) { return (isNaN(oFxNcL)) ? d : -d; }  
  34.         // rely on string comparison if different types - i.e. '02' < 2 != '02' < '2'  
  35.         else if (typeof oFxNcL !== typeof oFyNcL) {  
  36.             oFxNcL += '';  
  37.             oFyNcL += '';  
  38.         }  
  39.         if (oFxNcL < oFyNcL) return -d;  
  40.         if (oFxNcL > oFyNcL) return d;  
  41.     }  
  42.     return 0;  
  43. }  
Now, our function has the third parameter that defines the sort order.

Hereunder, I will describe the following simple procedure to use this JavaScript function with jqGrid (I used Visual Studio 2013, also added a simple HTML file, so if you want you can just use the HTML file):
  • Open Visual Studio.

  • Choose the template of your choice (I chose ASP.Net project).

  • Add the latest version of jqGrid using Nuget Packages, it will automatically add support for jQuery files.

  • Add the preceding JavaScript function as an inline function in the same file or in a separate file.

  • Add the code of jqgrid to call and make a call to this function.

jqgrid code

The following snippet is what the jqGrid code looks like:

  1. <script type="text/javascript">  
  2.         jQuery(document).ready(function () {  
  3.             jQuery("#list4").jqGrid({  
  4.                 datatype: "local",  
  5.                 height: 250,  
  6.                 colNames: ['Inv No''Date''Natural Sort order''Amount''Tax''Total''String Sort'],  
  7.                 colModel: [{ name: 'id', index: 'id', width: 60, sorttype: "int" },  
  8.                 { name: 'invdate', index: 'invdate', width: 90, sorttype: "date" },  
  9.                 { name: 'name', index: 'name', width: 150, sortfunc: naturalSort },  
  10.                 { name: 'amount', index: 'amount', width: 80, align: "right", sorttype: "float" },  
  11.                 { name: 'tax', index: 'tax', width: 80, align: "right", sorttype: "float" },  
  12.                 { name: 'total', index: 'total', width: 80, align: "right", sorttype: "float" },  
  13.                 { name: 'note', index: 'note', width: 150, sortable: "string" }],  
  14.   
  15.                 multiselect: true,  
  16.                 caption: "NaturalSortOrder using jqrid"  
  17.             });  
  18.   
  19.             var mydata = [  
  20.             { id: "1", invdate: "2013-10-01", name: "atest", note: "atest", amount: "200.00", tax: "10.00", total: "210.00" },  
  21.             { id: "2", invdate: "2013-10-02", name: "Atest", note: "Atest", amount: "300.00", tax: "20.00", total: "320.00" },  
  22.             { id: "3", invdate: "2013-09-01", name: "test2", note: "test2", amount: "400.00", tax: "30.00", total: "430.00" },  
  23.             { id: "4", invdate: "2013-10-04", name: "test", note: "test", amount: "200.00", tax: "10.00", total: "210.00" },  
  24.             { id: "5", invdate: "2013-10-05", name: "test2A", note: "test2A", amount: "300.00", tax: "20.00", total: "320.00" },  
  25.             { id: "6", invdate: "2013-09-06", name: "test2a", note: "test2a", amount: "400.00", tax: "30.00", total: "430.00" },  
  26.             { id: "7", invdate: "2013-10-04", name: "Test", note: "Test", amount: "200.00", tax: "10.00", total: "210.00" },  
  27.             { id: "8", invdate: "2013-10-03", name: "2", note: "2", amount: "300.00", tax: "20.00", total: "320.00" },  
  28.             { id: "9", invdate: "2013-09-01", name: "1", note: "1", amount: "400.00", tax: "30.00", total: "430.00" },  
  29.             { id: "10", invdate: "2013-09-01", name: "test34", note: "test34", amount: "400.00", tax: "30.00", total: "430.00" },  
  30.             { id: "9", invdate: "2013-09-01", name: "1test", note: "1test", amount: "400.00", tax: "30.00", total: "430.00" },  
  31.             { id: "11", invdate: "2013-09-01", name: "10", note: "10", amount: "400.00", tax: "30.00", total: "430.00" },  
  32.             { id: "12", invdate: "2013-09-01", name: "test33", note: "test33", amount: "400.00", tax: "30.00", total: "430.00" },  
  33.             { id: "13", invdate: "2013-09-01", name: "Test1", note: "Test1", amount: "400.00", tax: "30.00", total: "430.00" },  
  34.             { id: "14", invdate: "2013-09-01", name: "BTest", note: "BTest", amount: "400.00", tax: "30.00", total: "430.00" },  
  35.             { id: "15", invdate: "2013-09-01", name: "3", note: "3", amount: "400.00", tax: "30.00", total: "430.00" }];  
  36.   
  37.   
  38.             for (var i = 0; i <= mydata.length; i++)  
  39.                 jQuery("#list4").jqGrid('addRowData', i + 1, mydata[i]);  
  40.   
  41.         });  
  42.     </script>  
In the preceding, I added two sorting solutions, one is string sorting and the other is NaturalSort order, so we can understand the actual difference between the two algorithm or ways of sorting.

Data for sorting

We have the following data that needs to be sorted the Natural way:

Data for sorting

Data after string sorting

After String Sorting we have the following data:

Data after string sorting

Data after Natural Sorting

Here is data after Natural Sorting with the new feature of jqGrid:

Data after natural sorting

To use a natural sort with jqgrid, we need to write it this way:

  1. { name: 'name', index: 'name', width: 150, sortfunc: naturalSort }  

See in the sortfunc above, it is telling jqGrid that we need to use a naturalSort for our custom sort function. Isn't it easy?

What to do next?

See the latest release and its documentation, here:
  1. jqGrid 4.6.0 changes and fixes.
  2. jqGrid 4.7.0 Downloads.

The full source code is shown here, it is available at: jqGridNaturalSortOrder.

Closing Notes

  • In this entire article we saw how Natural sorting was invented as a new feature in jqGrid.
  • How to use it.
  • How it works.