Adding sorting capabilities to your table

One of the highly used features of Tabulizer is the client-side sorting capabilities that can add to your table. In other words, you can add a rule so table is sorted (ascending/descending order) by clicking on the table header row.

Rank Fastest time (s) Wind Athlete Country Date Location
1 9.58 +0.9 Usain Bolt Jamaica 16 August 2009 Berlin
2 9.69 +2.0 Tyson Gay United States 20 September 2009 Shanghai
3 9.72 +0.2 Asafa Powell Jamaica 2 September 2008 Lausanne
4 9.78 +0.9 Nesta Carter Jamaica 29 August 2010 Rieti
5 9.79 +0.1 Maurice Greene United States 16 June 1999 Athens
6 9.79 +1.5 Justin Gatlin United States 5 August 2012 London
7 9.80 +1.3 Steve Mullings Jamaica 4 June 2011 Eugene
8 9.84 +0.7 Donovan Bailey Canada 27 July 1996 Atlanta
9 9.84 +0.2 Bruny Surin Canada 22 August 1999 Seville
10 9.85 +1.2 Leroy Burrell United States 6 July 1994 Lausanne
11 9.85 +1.7 Olusoji Fasuba Nigeria 12 May 2006 Ad-Dawhah
12 9.85 +1.3 Mike Rodgers United States 4 June 2011 Eugene

In order to add soring capabilities, simple add a new rule to an existing ruleset with the following characteristics:

  • Element: table
  • Range: all
  • Table/Sort: Sort by Column # -> Any column number other than "none", sort order: Ascending or Descending. The sorting column/order selection refers to the initial sorting preferences that can change dynamically on the client side by the site user.

A few things to note:

  1. The sorting is dynamic and it's happening on the client side via Javascript code. As opposed to server-side sorting, the table can be sorted again if the visitor clicks on another header or change the order of the sorting (ascending vs descending) by clicking on the header twice.
  2. The sorting of the data is based on the detection of the data type, which can be extended or overridden by the Tabulizer developer. For instance, the string 12/10/2012 may be a date meaning 12th of October, 2012 or a date meaning 10th of December, 2012. It could also be a part number or anything else. Tabulizer makes an effort to guess right what is the data type, but some times this is not good enough as the data type exists only for your specific data set. If that is the case, you can define your own data type and the sorting functions using Javascript code in the jquery.dataTables.custom.js file that resides in the components/com_tabulizer/assets/js/custom folder for Joomla users, or the wp-content/plugins/tabulizer/site/assets/js/custom folder for Wordpress users.

    Here is the code to properly sort numbers with commas and periods, that may be enclosed in HTML tags, e.g. 1,000 1,100,090 1,234.56 <strong>1000</strong>. 
    function isCommaFormattedNumber (str) {       
  var r = /^[+-]?\d+(,\d{3})*\.?\d*$/;
    if (r.test(str)) return true;
    else return false;
}

function getCommaFormattedNumber (str) {
    var x = str.replace( /,/, "" );
    x = parseFloat( x );
    return x;
}

function parseCommaFormattedNumber(a, sorting_order) { 
    var empty_value = (sorting_order == 'asc') ? Number.MAX_VALUE : -Number.MAX_VALUE;
    a = a.replace(/<\/?[^>]+(>|$)/g, "").replace(/[\s|\+|%|\$\£\€]/g, "");
    if (( a == "-" ) || (a == "+") || (a == "") || (a == "&nbsp;")) a = empty_value;
    else a = getCommaFormattedNumber(a);
    return a;
}

jtQuery.fn.dataTableExt.aTypes.unshift(
    function ( sData )
    {               
        sData = sData.replace(/<\/?[^>]+(>|$)/g, "").replace(/[\s|\+|%|\$\£\€]/g, "");   
        if ((sData == "")||(sData == "&nbsp;")) return 'numeric-comma';       
        if (isCommaFormattedNumber(sData)) return 'numeric-comma';
        else return null;                                       
    }
);
   
jtQuery.fn.dataTableExt.oSort['numeric-comma-asc']  = function(a,b) { 
    var x = parseCommaFormattedNumber(a, 'asc'), y = parseCommaFormattedNumber(b, 'asc');   
    return ((x < y) ? -1 : ((x > y) ?  1 : 0));   
};

jtQuery.fn.dataTableExt.oSort['numeric-comma-desc'] = function(a,b) { 
    var x = parseCommaFormattedNumber(a, 'desc'), y = parseCommaFormattedNumber(b, 'desc');   
    return ((x < y) ?  1 : ((x > y) ? -1 : 0));       
};
    The key function here is the jtQuery.fn.dataTableExt.aTypes.unshift that takes as input the values of all the cells contained in the column being examined. If the value is a numeric one according to our rules, we return 'numeric-comma' otherwise we return null. There is no special meaning in the string 'numeric-comma', it 's just how we decided to name our data type. It could be anything else, like 'my-number', 'comma-separated-number', etc. The functions jtQuery.fn.dataTableExt.oSort['numeric-comma-asc'] and jtQuery.fn.dataTableExt.oSort['numeric-comma-desc'] define how to sort the date for the two different sorting orders, once the column cell values is determined that they match our data type. The rest of the functions are auxiliaries.

    Below it's the skeleton code for a new data type that we arbitrary named 'mydatatype': 
    function isMyDataType(str) {
    // return true is the str argument is of your custom data type, else return false  
}

function parseMyDataType(str) {
   // return a numeric value that will used in the sorting comparisons
}

jtQuery.fn.dataTableExt.aTypes.unshift(
    function ( sData )
    {               
       if  (isMyDataType(sData)) return 'mydatatype';
       else return null;
    }
);
   
jtQuery.fn.dataTableExt.oSort['mydatatype-asc']  = function(a,b) { 
    var x = parseMyDataType(a), y = parseMyDataType(b);
    return ((x < y) ? -1 : ((x > y) ?  1 : 0));   
};

jtQuery.fn.dataTableExt.oSort['mydatatype-desc'] = function(a,b) { 
   var x = parseMyDataType(a), y = parseMyDataType(b);   
    return ((x < y) ?  1 : ((x > y) ? -1 : 0));       
};

    For instance, as the default sorting is not treating as expected words that contain letters with acutes (e.g. Évora) you can specify your own data type to replace the acute before doing the text comparison:

    var Latinise={};Latinise.latin_map={"Á":"A","Ă":"A","Ắ":"A","Ặ":"A","Ằ":"A","Ẳ":"A","Ẵ":"A","Ǎ":"A","Â":"A","Ấ":"A","Ậ":"A","Ầ":"A","Ẩ":"A","Ẫ":"A","Ä":"A","Ǟ":"A","Ȧ":"A","Ǡ":"A","Ạ":"A","Ȁ":"A","À":"A","Ả":"A","Ȃ":"A","Ā":"A","Ą":"A","Å":"A","Ǻ":"A","Ḁ":"A","Ⱥ":"A","Ã":"A","Ꜳ":"AA","Æ":"AE","Ǽ":"AE","Ǣ":"AE","Ꜵ":"AO","Ꜷ":"AU","Ꜹ":"AV","Ꜻ":"AV","Ꜽ":"AY","Ḃ":"B","Ḅ":"B","Ɓ":"B","Ḇ":"B","Ƀ":"B","Ƃ":"B","Ć":"C","Č":"C","Ç":"C","Ḉ":"C","Ĉ":"C","Ċ":"C","Ƈ":"C","Ȼ":"C","Ď":"D","Ḑ":"D","Ḓ":"D","Ḋ":"D","Ḍ":"D","Ɗ":"D","Ḏ":"D","Dz":"D","Dž":"D","Đ":"D","Ƌ":"D","DZ":"DZ","DŽ":"DZ","É":"E","Ĕ":"E","Ě":"E","Ȩ":"E","Ḝ":"E","Ê":"E","Ế":"E","Ệ":"E","Ề":"E","Ể":"E","Ễ":"E","Ḙ":"E","Ë":"E","Ė":"E","Ẹ":"E","Ȅ":"E","È":"E","Ẻ":"E","Ȇ":"E","Ē":"E","Ḗ":"E","Ḕ":"E","Ę":"E","Ɇ":"E","Ẽ":"E","Ḛ":"E","Ꝫ":"ET","Ḟ":"F","Ƒ":"F","Ǵ":"G","Ğ":"G","Ǧ":"G","Ģ":"G","Ĝ":"G","Ġ":"G","Ɠ":"G","Ḡ":"G","Ǥ":"G","Ḫ":"H","Ȟ":"H","Ḩ":"H","Ĥ":"H","Ⱨ":"H","Ḧ":"H","Ḣ":"H","Ḥ":"H","Ħ":"H","Í":"I","Ĭ":"I","Ǐ":"I","Î":"I","Ï":"I","Ḯ":"I","İ":"I","Ị":"I","Ȉ":"I","Ì":"I","Ỉ":"I","Ȋ":"I","Ī":"I","Į":"I","Ɨ":"I","Ĩ":"I","Ḭ":"I","Ꝺ":"D","Ꝼ":"F","Ᵹ":"G","Ꞃ":"R","Ꞅ":"S","Ꞇ":"T","Ꝭ":"IS","Ĵ":"J","Ɉ":"J","Ḱ":"K","Ǩ":"K","Ķ":"K","Ⱪ":"K","Ꝃ":"K","Ḳ":"K","Ƙ":"K","Ḵ":"K","Ꝁ":"K","Ꝅ":"K","Ĺ":"L","Ƚ":"L","Ľ":"L","Ļ":"L","Ḽ":"L","Ḷ":"L","Ḹ":"L","Ⱡ":"L","Ꝉ":"L","Ḻ":"L","Ŀ":"L","Ɫ":"L","Lj":"L","Ł":"L","LJ":"LJ","Ḿ":"M","Ṁ":"M","Ṃ":"M","Ɱ":"M","Ń":"N","Ň":"N","Ņ":"N","Ṋ":"N","Ṅ":"N","Ṇ":"N","Ǹ":"N","Ɲ":"N","Ṉ":"N","Ƞ":"N","Nj":"N","Ñ":"N","NJ":"NJ","Ó":"O","Ŏ":"O","Ǒ":"O","Ô":"O","Ố":"O","Ộ":"O","Ồ":"O","Ổ":"O","Ỗ":"O","Ö":"O","Ȫ":"O","Ȯ":"O","Ȱ":"O","Ọ":"O","Ő":"O","Ȍ":"O","Ò":"O","Ỏ":"O","Ơ":"O","Ớ":"O","Ợ":"O","Ờ":"O","Ở":"O","Ỡ":"O","Ȏ":"O","Ꝋ":"O","Ꝍ":"O","Ō":"O","Ṓ":"O","Ṑ":"O","Ɵ":"O","Ǫ":"O","Ǭ":"O","Ø":"O","Ǿ":"O","Õ":"O","Ṍ":"O","Ṏ":"O","Ȭ":"O","Ƣ":"OI","Ꝏ":"OO","Ɛ":"E","Ɔ":"O","Ȣ":"OU","Ṕ":"P","Ṗ":"P","Ꝓ":"P","Ƥ":"P","Ꝕ":"P","Ᵽ":"P","Ꝑ":"P","Ꝙ":"Q","Ꝗ":"Q","Ŕ":"R","Ř":"R","Ŗ":"R","Ṙ":"R","Ṛ":"R","Ṝ":"R","Ȑ":"R","Ȓ":"R","Ṟ":"R","Ɍ":"R","Ɽ":"R","Ꜿ":"C","Ǝ":"E","Ś":"S","Ṥ":"S","Š":"S","Ṧ":"S","Ş":"S","Ŝ":"S","Ș":"S","Ṡ":"S","Ṣ":"S","Ṩ":"S","Ť":"T","Ţ":"T","Ṱ":"T","Ț":"T","Ⱦ":"T","Ṫ":"T","Ṭ":"T","Ƭ":"T","Ṯ":"T","Ʈ":"T","Ŧ":"T","Ɐ":"A","Ꞁ":"L","Ɯ":"M","Ʌ":"V","Ꜩ":"TZ","Ú":"U","Ŭ":"U","Ǔ":"U","Û":"U","Ṷ":"U","Ü":"U","Ǘ":"U","Ǚ":"U","Ǜ":"U","Ǖ":"U","Ṳ":"U","Ụ":"U","Ű":"U","Ȕ":"U","Ù":"U","Ủ":"U","Ư":"U","Ứ":"U","Ự":"U","Ừ":"U","Ử":"U","Ữ":"U","Ȗ":"U","Ū":"U","Ṻ":"U","Ų":"U","Ů":"U","Ũ":"U","Ṹ":"U","Ṵ":"U","Ꝟ":"V","Ṿ":"V","Ʋ":"V","Ṽ":"V","Ꝡ":"VY","Ẃ":"W","Ŵ":"W","Ẅ":"W","Ẇ":"W","Ẉ":"W","Ẁ":"W","Ⱳ":"W","Ẍ":"X","Ẋ":"X","Ý":"Y","Ŷ":"Y","Ÿ":"Y","Ẏ":"Y","Ỵ":"Y","Ỳ":"Y","Ƴ":"Y","Ỷ":"Y","Ỿ":"Y","Ȳ":"Y","Ɏ":"Y","Ỹ":"Y","Ź":"Z","Ž":"Z","Ẑ":"Z","Ⱬ":"Z","Ż":"Z","Ẓ":"Z","Ȥ":"Z","Ẕ":"Z","Ƶ":"Z","IJ":"IJ","Œ":"OE","ᴀ":"A","ᴁ":"AE","ʙ":"B","ᴃ":"B","ᴄ":"C","ᴅ":"D","ᴇ":"E","ꜰ":"F","ɢ":"G","ʛ":"G","ʜ":"H","ɪ":"I","ʁ":"R","ᴊ":"J","ᴋ":"K","ʟ":"L","ᴌ":"L","ᴍ":"M","ɴ":"N","ᴏ":"O","ɶ":"OE","ᴐ":"O","ᴕ":"OU","ᴘ":"P","ʀ":"R","ᴎ":"N","ᴙ":"R","ꜱ":"S","ᴛ":"T","ⱻ":"E","ᴚ":"R","ᴜ":"U","ᴠ":"V","ᴡ":"W","ʏ":"Y","ᴢ":"Z","á":"a","ă":"a","ắ":"a","ặ":"a","ằ":"a","ẳ":"a","ẵ":"a","ǎ":"a","â":"a","ấ":"a","ậ":"a","ầ":"a","ẩ":"a","ẫ":"a","ä":"a","ǟ":"a","ȧ":"a","ǡ":"a","ạ":"a","ȁ":"a","à":"a","ả":"a","ȃ":"a","ā":"a","ą":"a","ᶏ":"a","ẚ":"a","å":"a","ǻ":"a","ḁ":"a","ⱥ":"a","ã":"a","ꜳ":"aa","æ":"ae","ǽ":"ae","ǣ":"ae","ꜵ":"ao","ꜷ":"au","ꜹ":"av","ꜻ":"av","ꜽ":"ay","ḃ":"b","ḅ":"b","ɓ":"b","ḇ":"b","ᵬ":"b","ᶀ":"b","ƀ":"b","ƃ":"b","ɵ":"o","ć":"c","č":"c","ç":"c","ḉ":"c","ĉ":"c","ɕ":"c","ċ":"c","ƈ":"c","ȼ":"c","ď":"d","ḑ":"d","ḓ":"d","ȡ":"d","ḋ":"d","ḍ":"d","ɗ":"d","ᶑ":"d","ḏ":"d","ᵭ":"d","ᶁ":"d","đ":"d","ɖ":"d","ƌ":"d","ı":"i","ȷ":"j","ɟ":"j","ʄ":"j","dz":"dz","dž":"dz","é":"e","ĕ":"e","ě":"e","ȩ":"e","ḝ":"e","ê":"e","ế":"e","ệ":"e","ề":"e","ể":"e","ễ":"e","ḙ":"e","ë":"e","ė":"e","ẹ":"e","ȅ":"e","è":"e","ẻ":"e","ȇ":"e","ē":"e","ḗ":"e","ḕ":"e","ⱸ":"e","ę":"e","ᶒ":"e","ɇ":"e","ẽ":"e","ḛ":"e","ꝫ":"et","ḟ":"f","ƒ":"f","ᵮ":"f","ᶂ":"f","ǵ":"g","ğ":"g","ǧ":"g","ģ":"g","ĝ":"g","ġ":"g","ɠ":"g","ḡ":"g","ᶃ":"g","ǥ":"g","ḫ":"h","ȟ":"h","ḩ":"h","ĥ":"h","ⱨ":"h","ḧ":"h","ḣ":"h","ḥ":"h","ɦ":"h","ẖ":"h","ħ":"h","ƕ":"hv","í":"i","ĭ":"i","ǐ":"i","î":"i","ï":"i","ḯ":"i","ị":"i","ȉ":"i","ì":"i","ỉ":"i","ȋ":"i","ī":"i","į":"i","ᶖ":"i","ɨ":"i","ĩ":"i","ḭ":"i","ꝺ":"d","ꝼ":"f","ᵹ":"g","ꞃ":"r","ꞅ":"s","ꞇ":"t","ꝭ":"is","ǰ":"j","ĵ":"j","ʝ":"j","ɉ":"j","ḱ":"k","ǩ":"k","ķ":"k","ⱪ":"k","ꝃ":"k","ḳ":"k","ƙ":"k","ḵ":"k","ᶄ":"k","ꝁ":"k","ꝅ":"k","ĺ":"l","ƚ":"l","ɬ":"l","ľ":"l","ļ":"l","ḽ":"l","ȴ":"l","ḷ":"l","ḹ":"l","ⱡ":"l","ꝉ":"l","ḻ":"l","ŀ":"l","ɫ":"l","ᶅ":"l","ɭ":"l","ł":"l","lj":"lj","ſ":"s","ẜ":"s","ẛ":"s","ẝ":"s","ḿ":"m","ṁ":"m","ṃ":"m","ɱ":"m","ᵯ":"m","ᶆ":"m","ń":"n","ň":"n","ņ":"n","ṋ":"n","ȵ":"n","ṅ":"n","ṇ":"n","ǹ":"n","ɲ":"n","ṉ":"n","ƞ":"n","ᵰ":"n","ᶇ":"n","ɳ":"n","ñ":"n","nj":"nj","ó":"o","ŏ":"o","ǒ":"o","ô":"o","ố":"o","ộ":"o","ồ":"o","ổ":"o","ỗ":"o","ö":"o","ȫ":"o","ȯ":"o","ȱ":"o","ọ":"o","ő":"o","ȍ":"o","ò":"o","ỏ":"o","ơ":"o","ớ":"o","ợ":"o","ờ":"o","ở":"o","ỡ":"o","ȏ":"o","ꝋ":"o","ꝍ":"o","ⱺ":"o","ō":"o","ṓ":"o","ṑ":"o","ǫ":"o","ǭ":"o","ø":"o","ǿ":"o","õ":"o","ṍ":"o","ṏ":"o","ȭ":"o","ƣ":"oi","ꝏ":"oo","ɛ":"e","ᶓ":"e","ɔ":"o","ᶗ":"o","ȣ":"ou","ṕ":"p","ṗ":"p","ꝓ":"p","ƥ":"p","ᵱ":"p","ᶈ":"p","ꝕ":"p","ᵽ":"p","ꝑ":"p","ꝙ":"q","ʠ":"q","ɋ":"q","ꝗ":"q","ŕ":"r","ř":"r","ŗ":"r","ṙ":"r","ṛ":"r","ṝ":"r","ȑ":"r","ɾ":"r","ᵳ":"r","ȓ":"r","ṟ":"r","ɼ":"r","ᵲ":"r","ᶉ":"r","ɍ":"r","ɽ":"r","ↄ":"c","ꜿ":"c","ɘ":"e","ɿ":"r","ś":"s","ṥ":"s","š":"s","ṧ":"s","ş":"s","ŝ":"s","ș":"s","ṡ":"s","ṣ":"s","ṩ":"s","ʂ":"s","ᵴ":"s","ᶊ":"s","ȿ":"s","ɡ":"g","ᴑ":"o","ᴓ":"o","ᴝ":"u","ť":"t","ţ":"t","ṱ":"t","ț":"t","ȶ":"t","ẗ":"t","ⱦ":"t","ṫ":"t","ṭ":"t","ƭ":"t","ṯ":"t","ᵵ":"t","ƫ":"t","ʈ":"t","ŧ":"t","ᵺ":"th","ɐ":"a","ᴂ":"ae","ǝ":"e","ᵷ":"g","ɥ":"h","ʮ":"h","ʯ":"h","ᴉ":"i","ʞ":"k","ꞁ":"l","ɯ":"m","ɰ":"m","ᴔ":"oe","ɹ":"r","ɻ":"r","ɺ":"r","ⱹ":"r","ʇ":"t","ʌ":"v","ʍ":"w","ʎ":"y","ꜩ":"tz","ú":"u","ŭ":"u","ǔ":"u","û":"u","ṷ":"u","ü":"u","ǘ":"u","ǚ":"u","ǜ":"u","ǖ":"u","ṳ":"u","ụ":"u","ű":"u","ȕ":"u","ù":"u","ủ":"u","ư":"u","ứ":"u","ự":"u","ừ":"u","ử":"u","ữ":"u","ȗ":"u","ū":"u","ṻ":"u","ų":"u","ᶙ":"u","ů":"u","ũ":"u","ṹ":"u","ṵ":"u","ᵫ":"ue","ꝸ":"um","ⱴ":"v","ꝟ":"v","ṿ":"v","ʋ":"v","ᶌ":"v","ⱱ":"v","ṽ":"v","ꝡ":"vy","ẃ":"w","ŵ":"w","ẅ":"w","ẇ":"w","ẉ":"w","ẁ":"w","ⱳ":"w","ẘ":"w","ẍ":"x","ẋ":"x","ᶍ":"x","ý":"y","ŷ":"y","ÿ":"y","ẏ":"y","ỵ":"y","ỳ":"y","ƴ":"y","ỷ":"y","ỿ":"y","ȳ":"y","ẙ":"y","ɏ":"y","ỹ":"y","ź":"z","ž":"z","ẑ":"z","ʑ":"z","ⱬ":"z","ż":"z","ẓ":"z","ȥ":"z","ẕ":"z","ᵶ":"z","ᶎ":"z","ʐ":"z","ƶ":"z","ɀ":"z","ff":"ff","ffi":"ffi","ffl":"ffl","fi":"fi","fl":"fl","ij":"ij","œ":"oe","st":"st","ₐ":"a","ₑ":"e","ᵢ":"i","ⱼ":"j","ₒ":"o","ᵣ":"r","ᵤ":"u","ᵥ":"v","ₓ":"x"};
String.prototype.latinise=function(){return this.replace(/[^A-Za-z0-9\[\] ]/g,function(a){return Latinise.latin_map[a]||a})};
String.prototype.latinize=String.prototype.latinise;
String.prototype.isLatin=function(){return this==this.latinise()}

function isLocalStr(str) {
    // return true is the str argument is of your custom data type, else return false     
    return true;
}

function parseLocalStr(str) {
   // return a numeric value that will used in the sorting comparisons  
   return str.latinize();
}

jtQuery.fn.dataTableExt.aTypes.unshift(
    function ( sData )
    {              
       if  (isLocalStr(sData)) return 'local-str';
       else return null;
    }
);
  
jtQuery.fn.dataTableExt.oSort['local-str-asc']  = function(a,b) {    
    var x = parseLocalStr(a, 'asc'), y = parseLocalStr(b, 'asc');
    return ((x < y) ? -1 : ((x > y) ?  1 : 0));
   
};

jtQuery.fn.dataTableExt.oSort['local-str-desc'] = function(a,b) {
    var x = parseLocalStr(a, 'desc'), y = parseLocalStr(b, 'desc');   
    return ((x < y) ?  1 : ((x > y) ? -1 : 0));
};

    In this particular case, there is a better way to specify your data type using the localeCompare Javascript function that will make your code more robust and more compact. Below is the optimized version:

    jtQuery.fn.dataTableExt.aTypes.unshift(
    function ( sData )
    {              
       return 'local-str';      
    }
);
  
jtQuery.fn.dataTableExt.oSort['local-str-asc']  = function(a,b) {    
    return a.localeCompare(b);   
};

jtQuery.fn.dataTableExt.oSort['local-str-desc'] = function(a,b) {    
    return b.localeCompare(a);
};
    Important!
    If more than one custom data types return true, then only the last one will be applied. For instance, a column that contains only numbers it will return for the local-str data type we defined in this example, but also for the numeric-comma that will sort it numerically. Therefore it is important to define the local-str before the numeric-comma data type in the javascript file.