Primary/Secondary Sort & Null First (if Column is Desc) or Null Last (If Column is Asc)

good_fren

I'm doing Primary and Secondary Sort in the DataTable. The Primary Column is OrderNo and the Secondary Column is the Amount.

Along with this, I'm also doing the Null Sort:

Scenario:
1: If the Primary Column is Null and the Primary Column Sort Order is Ascending then the Null Values should be Displayed at the Bottom (Descending ) and the Non-Null Values should be displayed on the Top (Ascending)
2: If the Primary Column is Null and the Primary Column Sort Order is Descending then the Null Values should be Displayed at the Top (Ascending ) and the Non-Null Values should be displayed at the Bottom (Descending)

For This, I have used a DataTable Plugin

jQuery.extend( jQuery.fn.dataTableExt.oSort, {
"non-empty-string-asc": function (str1, str2) {
if(str1 == "")
return 1;
if(str2 == "")
return -1;
return ((str1 < str2) ? -1 : ((str1 > str2) ? 1 : 0));
},

"non-empty-string-desc": function (str1, str2) {
if(str1 == "")
return -1;
if(str2 == "")
return 1;
return ((str1 < str2) ? 1 : ((str1 > str2) ? -1 : 0));
}
} );

I'm able to Sort the Null Values however since, I'm also doing the Primary and Secondary Sort, the Secondary Sort is getting affected. Please See the Screenshot. When the Null Values are at the bottom (Primary Col is Ascending), the Secondary Amount Column is not Sorted and similarly the same issue when the Null is at the Top.

Any solutions or suggestions will be appreciated

kthorngren

I haven't dug through your code but might want to use the plugin described in the Sorting with absolute positioned data blog. If this doesn't help please provide a simple test case replicating your data and issue so we can help debug.
https://datatables.net/manual/tech-notes/10#How-to-provide-a-test-case

Kevin

good_fren

Please use this link for the JSFiddle. This is implementing the
jQuery.extend( jQuery.fn.dataTableExt.oSort
and Primary and Secondary Sort Code

https://jsfiddle.net/osushil/6o9ngdut/

kthorngren

Thanks for the test case. It does seem like what you have should work but I haven't figured out why its not. It looks like its not performing the secondary search and the data is staying in the table load order.
https://jsfiddle.net/xap03t4y/1/

I did notice one thing. The click event you have is causing the table to order twice. I added a order event so you can see this. I don't think its the issue though. I changed the example to turn orderable off on column 0 and used a flag to toggle between asc and desc. The behavior is the same except you see that everytime the sort goes to asc the secondary column data is reversed.
https://jsfiddle.net/xap03t4y/3/

Hopefully @allan or @colin can take a look and figure out the issue. The first is to find out if there is a best practice for creating your own click event. The second is why the secondary data seems to stay in order of the initial table load.

Kevin

colin

Hi both,

The problem is because the comparison with the empty strings suggests they're not the same (the 1 and -1 means they're bigger or smaller than other empty strings) - so they're not being grouped together so the secondary group has no effect since the first group has all unique values. Hope that makes sense.

I removed that empty string check, and it looks like it's doing what was required - can you take a look at this one here.

Hopefully that'll do the trick,

Cheers,

Colin

kthorngren

I did that too but thats not what @good_fren wants. The goal is to have the null values pushed to the bottom with ASC sorting. I agree they aren't being grouped for the secondary sorting. Just haven't thought o f a clever way to make it work yet.

Kevin

kthorngren

I think I got it. Based on @colin's comment:

the 1 and -1 means they're bigger or smaller than other empty strings

I created this updated example:
https://jsfiddle.net/dc1xrpj8/

Updated the plugin to look like this:

js "non-empty-string-asc": function (str1, str2) { if(str1 == "" && str2 != "") return 1; if(str2 == "" && str1 != "") return -1; if(str1 == "" && str2 == "") return 0; return ((str1 < str2) ? -1 : ((str1 > str2) ? 1 : 0));

Also note that I added columns.render to the Salary column so that it sorts properly by number not as a string.

You can change the secondary_column to any of the columns and it seems to work.

Kevin

good_fren

Thanks it works like Magic