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

Loren Maxwell

@allan -- how would I call this order reset from DT 2.x?

I've tried order().reset().draw() and order.reset().draw() (and even order.neutral().draw()) from a related blog post) but none worked.

allan

https://live.datatables.net/dovotuxe/1/edit

Pass an empty string as the sort direction. That isn't exactly what the commit I mentioned above is, but that is how to have the table not sorted.

The commit above was about the fact that the table will always go back to its original data loaded order before then applying an order. Also that when desc, ordering it will reverse that original order.

Allan

rf1234

https://live.datatables.net/pujohiba/1/edit

I just passed an empty array: The table isn't ordered. I also use that as an option and it works.

order: [], //no ordering by Data Tables

https://datatables.net/reference/option/order

Loren Maxwell

@allan , @rf1234 -- I didn't read the initial post closely so I misunderstood.

I thought the OP was looking for a way to reset the ordering to the initial ordering provided by the options:

https://live.datatables.net/dovotuxe/4/edit

Here the table goes to no order, but not to the initial order specified when the table is initialized.

My hope was something like:

var table = new DataTable('#example', {
    order: [[3, 'desc']]
});

table.order.reset()

Where the table would revert back to descending order on the fourth column.

Any suggestions for that?

rf1234

There is no such reset function. All you can do is to kind of "repeat" the option via the api.

table.order([3, 'desc']).draw();

rf1234

If you wanted to restore a custom saved order the user has defined earlier, and that was saved with "stateSaving", you can use tangerine's solution above.

Loren Maxwell

I'm realizing that now as I'm doing a closer reading of the initial posts.

I'd request a method be added to make it as simple as table.order.reset().draw() so that I could be agnostic to what the initial ordering was.

For my application I have two tables and a selection in one updates the other. It would be nice to easily reset the ordering along with the contents without having to necessarily know the initial order.

(Pseudocode below!)
table1.on('select', function() {
   table2.ajax.reload()
   table2.order.reset().draw()
})

rf1234

Ok, that could be a request. What I personally needed much more is dynamic ordering to avoid hard coding of columns to be ordered.

In that case you have the column to order by in a global variable anyway. Then you could do it like this for example: Ordering by update_time descending.

//find the location of update_time in the html table prior to dt initialization
//has to be set here because the update time column will be hidden, then the jquery
//command does not work any longer
var defaultOrder = [ $('#updateTime').index(), 'desc' ];

The option:
// sequence: update time desc, 
    order: [ defaultOrder ],

The reset:
table.order( defaultOrder ).draw();

kthorngren

@Loren Maxwell

You can use the init() API to get the Datatable init options. For your test case the result is:

{
    "order": [
        [
            3,
            "desc"
        ]
    ],
    "aaSorting": [
        [
            3,
            "desc"
        ]
    ]
}

You can use that the reset the table back to the original order. If you want to get fancy you could create an API plugin, for example:

DataTable.Api.register( 'order.reset()', function () {
    return this.iterator( 'table', function ( settings ) {
      table = new DataTable.Api(settings);
      order = table.init().order;
      table.order( order ? order : [[0, "asc"]] ).draw();
    });
} );

Running example:
https://live.datatables.net/fatuhada/1/edit

Kevin

allan

Impressive plugin Kevin! Even considers the fallback case that order wasn't set!

Allan

Loren Maxwell

@kthorngren -- that's exactly what I'm looking for. I hadn't even thought about init() since I was doing a search through the API for "order".

@allan -- I still encourage a built-in method!

Thanks!

allan

I don't recall this being asked for before, but if it is something that crops up as a common request, I'll certainly look at adding that. Until then, Kevin's plugin is bob on.

Allan

Loren Maxwell

"bob on" . . .

Ok, now this Yank has learned a little bit of UK slang as well!

This forum is amazing!

kthorngren

Yes the UK slang on this forum is quite fun to learn

Kevin

allan

Just wait until the Scots starts coming through

Allan