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

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

good_frengood_fren Posts: 9Questions: 4Answers: 0

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

This question has an accepted answers - jump to answer

Answers

  • kthorngrenkthorngren Posts: 20,141Questions: 26Answers: 4,735

    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_frengood_fren Posts: 9Questions: 4Answers: 0
    edited February 2019

    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/

  • kthorngrenkthorngren Posts: 20,141Questions: 26Answers: 4,735
    edited February 2019

    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

  • colincolin Posts: 15,112Questions: 1Answers: 2,583

    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

  • kthorngrenkthorngren Posts: 20,141Questions: 26Answers: 4,735

    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

  • kthorngrenkthorngren Posts: 20,141Questions: 26Answers: 4,735
    Answer ✓

    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_frengood_fren Posts: 9Questions: 4Answers: 0

    Thanks :smile: it works like Magic :smiley:

This discussion has been closed.