Sorting to ignore empty cells

liz

Thanks for writing such a useful piece of code.

Question: Is there any way that when sorting by a column, you can have any empty cells in the column always be last?

So when you clicked sort-order on a column, you'd have one of the following:

A
B
C
D
E
[empty]
[empty]
[empty]

E
D
C
B
A
[empty]
[empty]
[empty]

Thanks for your help

x

Rockb

Something I search for, too :-)

nikehair

I'm also looking for a way to do this.

fbas

you can write custom sort functions. sort functions just compare the values of 2 columns. return -1 if the first item is smaller, 0 if they are equal, and 1 if the first is greater. so for empty strings, always consider that less than the other.

define 2 functions. one for ascending, one for descending.

this type will be called 'mystring', but you can use any name you want
[code]
// install custom qaid sort routines
jQuery.fn.dataTableExt.oSort['mystring-asc'] = function(x,y) {
if (x==y) return 0;
if (x == "") return -1;
if (y == "") return 1;
if (x > y) return 1;
}
jQuery.fn.dataTableExt.oSort['mystring-desc'] = function(y,x) {
if (x==y) return 0;
if (x == "") return -1;
if (y == "") return 1;
if (x > y) return 1;
}

[/code]

then in your column definitions, associate this type with a column:
[code]
$(document).ready(function() {
$('#example').dataTable( {
"aoColumns": [
null,
null,
{ "sType" : "mystring" }
]
} );
} );
[/code]

http://www.datatables.net/plug-ins/sorting#how_to_type

Rockb

Hi fbas, thx for this function. But actually it doesn't work in my case. I put the function in to the separated file dataTables.numericComma.js which is already included in the script. And in the column (as described in your example) the "sType" : "mystring" - but it still doesn't ignore the empty fields by clicking the column header to sort it.

fbas

you're right. I made some small errors in the code. Here is version 2, which I tested (it's not perfect, but keeps "empty" cells at the bottom):
[code]
jQuery.fn.dataTableExt.oSort['mystring-asc'] = function(x,y) {
var retVal;
x = $.trim(x);
y = $.trim(y);

if (x==y) retVal= 0;
else if (x == "" || x == " ") retVal= 1;
else if (y == "" || y == " ") retVal= -1;
else if (x > y) retVal= 1;
else retVal = -1; // <- this was missing in version 1

return retVal;
}
jQuery.fn.dataTableExt.oSort['mystring-desc'] = function(y,x) {
var retVal;
x = $.trim(x);
y = $.trim(y);

if (x==y) retVal= 0;
else if (x == "" || x == " ") retVal= -1;
else if (y == "" || y == " ") retVal= 1;
else if (x > y) retVal= 1;
else retVal = -1; // <- this was missing in version 1

return retVal;
}
[/code]


I don't always have time to test the code I post, and I apologize for that.

make sure your empty string cells are actually empty strings (or update the functions if your cells have some other space or non-visible character in it

Rockb

Wonderful! It works.

Just a tiny question: Relied to what column are the other empty lines are sorted by? It seems the next column?

fbas

just relative to each other (column A compared to column B).

Rockb

Sry, I don't get it :-D JavaScript is too complicated for me. My solution is mostly: try&error.

I have e.g. three columns, the 2nd have empty fields. With your function, the 2nd column don't show empty fields (I add "sType" : "mystring" in the "aocolumns"). But instead of the third column I'd like to sort via the 1st colum for alle the empty fields in the 2nd column.

fbas

It's possible to tell DataTables to always sort by a given column first, which might be what you want:

if you set aaSortingFixed, the "fixed" column will always be sorted first, then other columns will be sorted in relative position to that "fixed" column.

[code]
$(document).ready( function() {
$('#example').dataTable( {
"aaSortingFixed": [[0,'asc']]
} );
} )


$(document).ready(function() {
$('#example').dataTable( {
"aoColumns": [
{ "sType" : "mystring" }, // if you don't want the first column sorted with the
null,
{ "sType" : "mystring" }
],
"aaSortingFixed": [[0,'asc']]
} );
} );

[/code]

is this what you mean?

----------
There is also something more complicated to help the custom oSort functions above. (without adding an afnSortData function, those just compare values in the same column.)

The sort function extension afnSortData that gives you access to the oSettings object, which lets you look into other columns. You need to create an array of values that will be sorted, in a sense you are re-creating the data of column you want to sort to reflect values that you want to sort by. You can do this by combining values or replacing values, etc. The function below uses column 0 to sort by (similar to the example above, so this is not a very useful version).
[code]
$.fn.dataTableExt.afnSortData['get_col_zero'] = function ( oSettings, iColumn )
{
var aData = [];
$( 'td:eq(0)', oSettings.oApi._fnGetTrNodes(oSettings) ).each( function () {
aData.push( this.innerHTML );
} );
console.log(aData);
return aData;
}
[/code]

If you can explain a little more about what you're trying to do, I might be able to help more.

Rockb

Okay, I try my best to describe it. Sorry, and also thx for your examples!
I already use "aaSorting" for the third column. These are the columns:

NAME | GROUPE | AMOUNT

With "aaSorting" it's ordered by the AMOUNT if you open the table (that's the standard).
The 2nd column GROUPE have empty fields. That's why I need your function.

BUT: If I sort by GROUPE, I don't want to sort the empty fields in GROUPE by the AMOUNT... they should be sorted by the first colum... the Name (ASC). This makes more sense. Even for similar columns, the 2nd order should be the first column.

Example:

ALPHA | GROUPE1 | 741
BETA | *EMPTY* | 564
DELTA | *EMPTY* | 385
GAMMA | GROUPE1 | 345
BINGO | GROUPE2 | 457

If I order by GROUPE, the result should look like this:

ALPHA | GROUPE1 | 741
GAMMA | GROUPE1 | 345
BINGO | GROUPE2 | 457
BETA | *EMPTY* | 564
DELTA | *EMPTY* | 385

fbas

you can do something like this (assuming none of your values in the 2nd column start with '{', I use it as a special character)

The afnSortingData code uses 2nd column to sort by, but IF the column is empty, use the value "{" + first column value. like this (but doesn't show it to the user):
[code]
ALPHA | GROUPE1 | 741
GAMMA | GROUPE1 | 345
BINGO | GROUPE2 | 457
BETA | {BETA | 564
DELTA | {DELTA | 385
[/code]

Then the oSort functions treat values that start with "{" as items to push to the bottom.

[code]
$.fn.dataTableExt.oSort['mystring-asc'] = function(x,y) {
var retVal;
x = x.replace(' ', '');
y = y.replace(' ', '');

if (x == y) retVal = 0;
else if (x.substr(0,1) == "{" && y.substr(0,1) == "{") {
if (x > y) retVal= 1;
else retVal = -1;
}
else if (x.substr(0,1) == "{") retVal = 1;
else if (y.substr(0,1) == "{") retVal = -1;

else if (x > y) retVal= 1;
else return -1;

return retVal;
}
$.fn.dataTableExt.oSort['mystring-desc'] = function(y,x) {
var retVal;
x = x.replace(' ', '');
y = y.replace(' ', '');

if (x == y) retVal= 0;
else if (x.substr(0,1) == "{" && y.substr(0,1) == "{") {
if (x > y) retVal= -1;
else retVal = 1;
}
else if (x.substr(0,1) == "{") retVal = -1;
else if (y.substr(0,1) == "{") retVal = 1;

else if (x > y) retVal = 1;
else return -1;

return retVal;
}


$.fn.dataTableExt.afnSortData['mystring'] = function ( oSettings, iColumn )
{
var aData = [];

$( 'td:eq('+iColumn+')', oSettings.oApi._fnGetTrNodes(oSettings) ).each( function () {
var value = this.innerHTML;
value = value.replace(' ', '');

if (!value) value = '{' + $(this).parent().children()[0].innerHTML;

aData.push( value );
} );

return aData;
}


$(document).ready(function() {
$('#example').dataTable( {
"aoColumns": [
null,
{
sType : "mystring",
sSortDataType: "mystring" // these names "mystring" don't have to be the same, just need to match the sort functions and afnSortData function, respectively
},
null
]
} );
} );




[/code]

kingmook

Just wanted to quickly say thank you to fbas for a fairly painless solution.

alanomaly

Some great code from fbas there. It works great for strings, if you need something that works for decimal numbers, try this:
[code]
jQuery.fn.dataTableExt.oSort['NumericOrBlank-asc'] = function(x,y) {
var retVal;
if (y = parseFloat($.trim(y).replace(/,/g,''))) {
x = (x = parseFloat($.trim(x).replace(/,/g,''))) ? x : 0;
if (x==y) retVal= 0;
else retVal = (x>y) ? 1 : -1;
} else {
retVal = -1;
}
return retVal;
}
jQuery.fn.dataTableExt.oSort['NumericOrBlank-desc'] = function(y,x) {
var retVal;
x = (x = parseFloat($.trim(x).replace(/,/g,''))) ? x : 0;
y = (y = parseFloat($.trim(y).replace(/,/g,''))) ? y : 0;
if (x==y) retVal= 0;
else retVal = (x>y) ? 1 : -1;
return retVal;
}
[/code]
This covers English style numbers where 50,001 means fifty thousand and one. For those languages where 50,001 would mean fifty point zero zero one and 50.001 would mean fifty thousand and one, just swap each place where it says .replace(/,/g,'') to .replace(/./g,'').replace(/,/g,'.') (untested but should work)

alanomaly

...typo correction above