Equivalent of data-order for ajax sourced data

gb691

Hi,

I've used datatables before with server-side processing where date ordering for cells that contain datesis enforced by adding a data-order="mySQLDate.format("yyyymmdd")" so that the built table looks like:

<tr>
        <td data-order="20220131">31st January 2022</td>
</tr>

How is this achieved when sourcing the data from Ajax? Is there something I can put in columns : [] to tell datatables how to sort the data? Does datatables do type inference when it comes to sorting?

Apologies if this is all in the manuals; I couldn't find anything that exactly answered my question.

allan

For Ajax data, the key is to use orthogonal data. The HTML attributes way of doing that actually uses the object based orthogonal data way of doing it under the hood!

The key is to have the two pieces of information that you want to use in the JSON data source. Then you can tell DataTables which one you want it to use for each type. The "Predefine values" section of the manual page I linked to shows how to do that.

There is also an example, which you might find more directly applicable.

Since there are many ways of doing the same thing, an alternative approach is to have the server return an ISO8601 date, and let the client-side render it. You can use auto locale display to have the date rendered in a way the end user might expect to see it, or you can specify a particular format if you want it fixed. Ordering is automatic is such a case, since DataTables "understands" ISO8601. Personally I like using ISO8601 as the "wire" data, and allow the locale formatting to happen at the last moment for the client.

Whichever way you decide to do it, let me know how you get on or if you have any follow up questions!

Regards,
Allan

gb691

Hi @allan,

Many thanks for your response! I actually found the answer to my problem 5 minutes after I submitted this question (isn't that always the way!). I had seen the orthogonal data page, but given that I'm not able to produce orthogonal data because I don't have control of the JSON in this instance, I had concluded it was not the way to go. However, I then noticed the "Computed values" section underneath which explicitly addresses the issue of not being able to produce orthogonal data.

Anyway, sorry to have wasted your time, but thanks again for your help.

Regards,
Guy

allan

Not at all - glad to hear that you've got the solution for your use case

Allan

gb691

Sorry, @allan,

One last question. So by using render: DataTable.render.date() in columns: [ ] does that just change the way that the date is displayed and the sorting is still technically being done on the ISO8601 formatted date? The reason I ask is because I've written code before like the following:

render: function(data, type, row) {
    if(type === "display" || type === "filter") {
        var d = new Date(Date.parse(data));
        return d.toLocaleDateString("en-GB");
    }
    return data;
},

Which is only changing the format for display and filter "types" but, I guess, leaving the format unchanged for "sort" types? How does render: DataTables.render.date() behave? Is it doing the render for all "types" or only for "display". I mean, I've implemented it and it's working beautifully, this is just for my own curiosity really.

Regards,
Guy

allan

does that just change the way that the date is displayed and the sorting is still technically being done on the ISO8601 formatted date?

More or less, yes. This is the function that the date renderer resolves to. Specifically for display data types it will escape any HTML in the string, while for the filter type it just uses the rendered value (no escaping).

That can be seen in this example where you can search for the formatted data, but searching for the ISO date will not match.

Your implementation boils down to something very similar to my own

Allan