How do I reset the table sort to initial sort order

retrospaceman

Link to test case:
Here's a JSFiddle of the problem I am having:
https://jsfiddle.net/retrospaceman/xn1y3o0d/

Description of problem:

When the table is loaded, we are default sorting it by the date (descending).

When loaded the order of the number column is: 1,2,3,4,5,6 which is what we want

If you sort by the suburb (ascending) column, and then sort by the date column for descending again, now the order of the number column is: 2, 1, 4, 3, 6, 5 which is NOT what we want.

I assume the issue is because some entries share the same date, but it only occurs after sorting by the suburb column (ascending).

I figured the order of the data needs to be reset to its initial state when it was loaded in before applying the date sort so that its not sorting data thats already been sorted thus resulting in the weirdness but I have not been able to accomplish it so far.

Things I have tried:

The order neutral plugin: https://datatables.net/plug-ins/api/order.neutral()

JSFiddle here:
Order neutral plugin test: https://jsfiddle.net/retrospaceman/8ck1rg4p/

This DOES WORK if I manually (via the JSFiddle console) execute $("#testTable").DataTable().order.neutral() after every time I sort by suburb (Note: we don't call draw because we only want to change the order in the background so its ready for the next sort).
See the JSFiddle for comments on testing manually (NOTE: JSFiddle takes a few seconds to execute the function).

But DOES NOT WORK if I try to programatically do it and I cannot for the life of me figure out why. The obvious solution is to call it after each draw where the table was sorted which I have done (See here, Order neutral plugin test: https://jsfiddle.net/retrospaceman/8ck1rg4p/).

But this instead results in the user being unable to sort more than once, after the first sort by date you can now no longer sort by date at all (the same for suburb), so programatically calling it breaks the table for some reason.

I've tried placing $("#testTable").DataTable().order.neutral() in a setTimeout (up to 1000ms I've tried) thinking maybe I need to let some background process end but its still the same, the table sorting is broken.

---

So, how do I make it so that whenever the user sorts by date (or any column for that matter), the sort is applied to the original order that the data was in when it was loaded?

Am I overlooking some other ability datatables has? do I not have my custom sort set up correctly? I've been beating my head at the wall on this for the last two days and just keep going around in a circle, any help is appreciated, thank-you.

Apology note:

Apologies for the duplicate posts, I kept linking the wrong JSFiddle versions as any change increments the version number so I needed to link the right one. And then the edits I made here makes the post disappear as it needs to be approved (which made me think I deleted it). Sorry to the admins, mods and everyone else for the confusion, this should be the final edit.

allan

The issue you are seeing is due to that fact that DataTables implements a stable sort on the data that has already been sorted. So once the data has been sorted by Suburb it has a specific order. Now sorting on the Date column again, if there are any values that are equal in value, DataTables will intentionally maintain their position relative to each other.

Many moons ago we had an "unstable sort" whereby sorting on a column with equal data in multiple cells could cause their position to change - just when resorting the same column. That really confused things (although was faster).

What we could possibly could do is 'de-sort' the data into its original order before performing a full sort, and then do the full sort. I expect the performance impact would be negligible on small data sets, but might have more of an impact on larger ones.

Instead, what I would recommend is using

• columns.orderData to tell DataTables that a second column's data should be considered for sorting when the first column's data is equal, and
• columns.orderSequence to alter the asc / desc order to match your initial setup.

This is the updated example.

That said, I can see the advantage of doing a 'de-sort', and I am going to look into that further. Thanks for bringing it up!

Allan

tangerine

Not sure this is entirely relevant, but I'm doing this

        initComplete: function (settings) {

            // Save the initial sort sequence to the browser's session storage.
            sessionStorage.setItem('initialSort', JSON.stringify(settings.aaSorting));
            },   

which means I can retrieve and reset the original sequence if or when required.

rf1234

@tangerine
Cool solution! Many thanks.

tangerine

Thanks! That's pretty special coming from you.

retrospaceman

Allans suggestions of using orderData solved the problem for me, thanks Allan!

I had read the docs on orderData before and dismissed it because it didn't click with me that it would solve the issue, only after trying it and realising that by sorting by a second column helps maintain consistency for the first column with equal values.

its probably just me, but maybe an update to the docs for orderData to explain its usefulness in the kind of situation I found myself in would be useful for other people?

Either way, thanks heaps for the quick response Allan.

allan

Agreed - thanks for the suggestion. That's it committed in and the site will be updated with the next push.

Allan

retrospaceman

Amazing work Allan, thanks for all the help

allan

Quick update, I've committed the "reset" that I had mentioned before into the DataTables 2 branch. I didn't want to include it in 1.x as it is a change in behaviour, but I think it is the correct change, so it will be in 2 .

Allan