Does -pre know the direction of sorting being applied?

guillochon

I want to put empty entries always at the bottom of the sort. However, this doesn't seem possible with -pre, as it doesn't know the sort direction. How can I accomplish this?

jr42.gordon

jQuery.extend( jQuery.fn.dataTableExt.oSort, {

"num-html-asc": function ( a, b ) {

      return ((a < b) ? -1 : ((a > b) ? 1 : 0));

},

"num-html-desc": function ( a, b ) {

      return ((a < b) ? 1 : ((a > b) ? -1 : 0));

});

guillochon

Hi jr42, no that won't work, according to the devs -pre and -asc/-desc are mutually exclusive, they will not work together: https://datatables.net/forums/discussion/21104/custom-sorting-asc-desc-not-called-when-pre-is-present

jr42.gordon

That is why -pre is excluded in the code snippet I provided. Allan is correct in that providing this will not call -asc & -desc.

So in the snippet I provided, if a or b is blank, you set it to a low/high value depending on the direction of the sort.

guillochon

So the answer to the question is "no, -pre is not aware of the sort direction." Why? It's far less efficient to pre-process in -asc and -desc because you have to do it multiple times for each entry. With -pre you do it once.

jr42.gordon

I cannot find the article I read this from.

The first time you sort a column, -pre is run and the resulting array of column values is cached, then the sort direction is performed. From this point on, the cached array is utilized for sorting and -pre is not called again for that column. Unless you .invalidate() the column, but that is a completely separate discussion.

I agree with you that in theory, we should be able to provide a -pre, -asc, and -desc event handler, and have each fire appropriately because of the roll each play in the sorting of a column. Our events should also be able to be utilized the same way, and with the same cached data, that Datatables does it.

allan

It is possible with -pre, but it is really the -desc and -asc functions that do the sort, so it is them that would need to put the rows in the order you require.

-pre is really just a deformatter which is common to the -desc and -asc code. For example consider sorting HTML data - you could remove the HTML in both functions, but it takes less code to just do it once in a -pre method. It also allows DataTables to perform other performance optimisations such as the caching that @jr42.gordon mentions.

Allan

guillochon

Hi I'm still not really satisfied with this, I have to do expensive strip operations every time the table is sorted if I want to customize the sort order. pre, desc, and asc should not be mutually exclusive, it doesn't make any sense.

allan

Your -pre would still need to do the strip call for every item in the table on every order action - that function is called whenever DataTables sorts a column.

If you don't want to do any formatting at that point, then orthogonal data can be used.

Allan

guillochon

That's OK, it's better than calling it N^2 times (or N log N?) by putting it in -desc or -asc.

Also @allan, your link is a 404?

allan

Performance was the main reason for adding the -pre option. Waiting to sort by different criterion between the two directions has, in my experience, been very rarely used, hence why it isn't optimised for that use case.

404: Sorry - link fixed.

Allan

guillochon

(accidentally responded to wrong thread)

guillochon

OK, so maybe I'll open another question for this, but I just cannot figure out how to sort blanks at the bottom of a column that's numeric. -pre is mutually exclusive with -asc and -desc, and data orthogonality also doesn't work (I cannot make two orthogonal datasets for the ascending and the descending sorts).

The only thing that half works is to convert all empty entries to "NaN" in -pre. However, this only works for the first sort performed by the user, sort by any other column and then sort again by the column with blanks, and the sorting is broken (it is random).

I do not buy the "this is a rare use case" argument; people want to sort the blank entries at the bottom of a column quite frequently.

allan

I've just been looking into what would be involved in sending the sort direction to the -pre formatting function, and it unfortunately isn't a trivial change - at least not if it is done correctly :-). Passing the direction in is easy, but the problem is that the returned data is stored so it can be quickly looked up, and it is stored per column. So if you were to have different formatters for the two directions, one would overwrite the others (hence why it isn't a trivial change to support this).

Also, my comment about above -pre always being called on all data points for every sort is incorrect. It is only called when the data has been invalidated or on first sort of that column (so actually a bit more optimal than I remember - trading memory for speed).

Possibly the way forward here, relates to your comment on the sorting plug-ins page (thanks for that - I'll fix it shortly) - the -pre formatter should prep the string for use (doing the strip or whatever) and if present -asc and -desc should be used. At the moment the priority is the other way around - -pre is the one that is optional since it is a later addition to DataTables.

I need to have a think about if I want to put this change into 1.11. I've actively planning the next major version and I think this should probably go into that - the downside is that I'm also working on other aspects and thus it won't be implemented immediately.

Allan

jr42.gordon

@allan

I understand the functionality behind -pre and agree with it. However, the solution that I feel would alleviate his issue would be the ability for our custom -pre, -asc, -desc functions to all be called.

We would use -pre to format the data accordingly, then if need be, provide additional formatting for "special" data depending on direction of sort, -asc or -desc.

Here is an example of what I mean. End result is to always have '0' values sort to bottom, regardless of sort direction.

jQuery.extend( jQuery.fn.dataTableExt.oSort, {
 
"num-html-pre": function ( a ) {

     // formats number: positive, negative, zero

},

"num-html-asc": function ( a, b ) {
 
      // if neither are zero, perform following; 
      return ((a < b) ? -1 : ((a > b) ? 1 : 0));

      //else zero number is lower
 
},
 
"num-html-desc": function ( a, b ) {
 
      // if neither are zero, perform following; 
      return ((a < b) ? 1 : ((a > b) ? -1 : 0));

      //else zero number is higher
 
});

What would it take to achieve this functionality?

allan

The modification to DataTables that I mentioned above. The sorting function will need to be modified to allow -pre to also work with -asc and -desc.

Allan

guillochon

@allan: I think my solution can work for now if I figure out a way to clear the render cache every time a user sorts (as I said, it works on the first sort, and fast!). How would I go about forcing a cache flush every time the table is sorted?

guillochon

@allan: If you want to see what's happening now where I'm just converting blanks to NaNs, visit this page [https://sne.space] and sort the "dL" column, then sort the "disc. date" column, then sort the "dL" column again. Notice how the "dL" column is not sorted the second time.

guillochon

AH HA! I've figured this out.

The key is to make a render function that first converts blanks to NaNs. Then, when checking for NaNs in -desc and -asc, you must have a check for both equaling NaN in addition to the single checks, i.e.

if(isNaN(v1) && isNaN(v2))
    return 0;
if(isNaN(v1))
    return 1;
 if(isNaN(v2))
    return -1;

If you don't have the first check, if(isNaN(v1) && isNaN(v2)), the sort is excruciatingly slow when your column has a lot of blanks, most likely because every blank is sorted all the way to the bottom of the table each time, even past other blank entries (which is a waste!).

allan

Good to hear you've got it working now :-)

Allan