Sorting with absolute positioned data

The ordering of information in a DataTable is probably the aspect that I cover most in this blog (enums and dates for example, and more to come), but that is because it such a rich topic! DataTables is used in a wide range of applications from space telescope telemetry to amateur football leagues, so it needs to be able to cope with lots of different data types. DataTables core ships with just a basic set of data sorting functions (string, number, currency, percentage, ISO8601 dates) so, for anything outside of this range DataTables provides a sorting plug-in API that you can use to define your own sorting methods.

Typically with a sorting method you would have linear ordering, whereby the descending sort is the exact reverse of ascending, and everything is ordered by a simple comparison (numeric or character code points). This post is going to be a little different: I'll introduce a non-linear configurable sorting plug-in, whereby you can keep specific pieces of data at the top or the bottom of a column, regardless of the sorting direction and the value of that item.

Let's do this post entirely backwards! We'll start with a finished example, then describe the API should you just want to use it without any implementation details and finally, if you are interested in more technical aspects (of course you are - this is a developer blog!) I'll discuss the implementation.

Example

An example is always worth a million words, so let's consider the following table:

Name	Position	Office	Start date	Salary
Tiger Nixon	System Architect	Edinburgh	2011/04/25	$320,800
Garrett Winters	Accountant	Tokyo	2011/07/25	$170,750
Ashton Cox	Junior Technical Author	San Francisco	2009/01/12	$86,000
Unknown	Senior Javascript Developer	Edinburgh	2012/03/29	TBC
Airi Satou	Accountant	Tokyo	2008/11/28	$162,700
Brielle Williamson	Integration Specialist	New York	2012/12/02	$372,000
Unknown	Sales Assistant	San Francisco	2012/08/06	TBC
Rhona Davidson	Integration Specialist	Tokyo	2010/10/14	$327,900
Colleen Hurst	Javascript Developer	San Francisco	2009/09/15	Not available
Sonya Frost	Software Engineer	Edinburgh	2008/12/13	$103,600

In this case we have two columns where absolute ordering can be useful:

• Name column where there are "Unknown" entries that should be shown at the top of the table.
• Salary column where "TBC" should be shown at the top and "Not available" should be shown at the bottom.

If you alter the sorting that is applied to the table by clicking on the headers of those columns, you will be able to see that those items stay in position regardless of the sorting of the column. If sorting is applied to one of the other columns the rows will be sorted normally.

Why

Okay it works, but why would you want to do this? Primarily to highlight certain data - for example missing data that needs to be filled in (using Editor - that's the only sales pitch in this post!). It might also be useful to demote some data into obscurity, such as the "Not available" salary shown above.

How to use it

The first thing to do is to include the plug-ins' source on your page. It is available on the DataTables CDN here:

To allow for each different data set being different, the plug-in needs to be configured to tell it what to keep at the top and / or bottom of the table. This is done through two functions:

• DataTable.absoluteOrder for ordering strings
• DataTable.absoluteOrderNumber for ordering numbers.

These two methods each accept a single argument that can be one of:

• string - A value that should be kept at the top of the table for a column.
• object - An object that has the following properties:
  • value - The value to keep at the top or bottom of the table
  • position - Position that this data should keep in the sorting order - top or bottom.
• array - An array of strings or objects matching the above description to allow multiple values to be treated specially in the sorting.

The function will construct and attach a sorting plug-in for DataTables that will perform the sorting required. However, one important point is that this plug-in does not do automatic type detection. Instead it will return a value that should be assigned to the columns.type option for the column that should perform this kind of sorting. This is an important point - most other plug-ins announced in this blog will do automatic type detection, but there is no way to reliably and efficiently detect columns that match those suited for this plug-in due to their dynamic nature and the fact that you might not wish it to be applied to all columns that happen to match the values used.

Example usage

The most simple use case is to have a specific piece of data sorted at the top of a column:

var nameType = DataTable.absoluteOrder( 'Unknown' );

$('#myTable').DataTable( {
    columnDefs: [
        { targets: 0, type: nameType }
    ]
} );

Or if you want an item to be at the bottom of a table:

var nameType = DataTable.absoluteOrder( {
    value: 'Unknown', position: 'bottom'
} );

$('#myTable').DataTable( {
    columnDefs: [
        { targets: 0, type: nameType }
    ]
} );

Example code

The code used for the above example uses a combination of all three options for how the configuration can be set up; since the salary column as data positioned both at the top and bottom of the column an array is used. It is shown below:

var nameType = DataTable.absoluteOrder( 'Unknown' );
var salaryType = DataTable.absoluteOrderNumber( [
    'TBC',
    { value: 'Not available', position: 'bottom' }
] );

$('#myTable').DataTable( {
    columnDefs: [
        {
            targets: 0,
            type: nameType
        },
        {
            targets: 5,
            type: salaryType
        }
    ]
} );

And that's really all there is to it!

How it works

Making a sorting plug-in for DataTables is really quite easy; that's not the interesting part of this software, but it is key that we understand exactly how sorting works in Javascript - specifically how we can pass a custom function to Array.prototype.sort() to define how we want data to be ordered. As always, the excellent MDN has a great description:

• If compareFunction(a, b) is less than 0, sort a to a lower index than b, i.e. a comes first.
• If compareFunction(a, b) returns 0, leave a and b unchanged with respect to each other, but sorted with respect to all different elements.
• If compareFunction(a, b) is greater than 0, sort b to a lower index than a.

So what we need to do is determine if either parameter passed into the sorting function is one that needs to be at the top or bottom of the sort array. That could be done with a simple for loop, but that is an O(n) solution to the problem, and in a sort comparison function we want things to be as fast as possible since it is executed a lot, particularly for larger data sets. To make it O(1) we can prepare an object that contains the values to be searched for as parameter names which can then be checked for using a trivial lookup. For example imagine we have var o = { "Unknown": true }, to check if a value is in the object we can simply use if ( o[value] ) {...}.

So to built up the required objects we can use:

var alwaysTop = {};
var alwaysBottom = {};

for ( var i=0, ien=values.length ; i<ien ; i++ ) {
    var conf = values[i];

    if ( typeof conf === 'string' ) {
        alwaysTop[ conf ] = true;
    }
    else if ( conf.position === undefined || conf.position === 'top' ) {
        alwaysTop[ conf.value ] = true;
    }
    else {
        alwaysBottom[ conf.value ] = true;
    }
}

Then our sorting functions can simply contain:

function ( a, b ) {
    if ( o.alwaysTop[ a ] || o.alwaysBottom[ b ] ) {
        return -1;
    }
    else if ( o.alwaysBottom[ a ] || o.alwaysTop[ b ] ) {
        return 1;
    }

    return ((a < b) ? -1 : ((a > b) ? 1 : 0));
};

Everything else in the plug-in is just packaging to allow number based sorting, UMD loading and to keep the code size relatively small.

Room for improvement

There is always a way to make something better, and if you have any suggestions or contributions for this plug-in, please feel free to send a pull request! The source is available on GitHub. Alternatively, if you have any ideas for your own sorting plug-ins, they will be very welcome.

Happy Holidays everyone!