Date sorting non-functional on larger tables.

Clusterfaff

Currently working on a Vue3 Script Setup Composition API project with multiple table components. Noticed the other day that the date columns don't order properly and so worked to fix this issue (namely that they are delivered in the format of 18 Dec 2024, rather than being ISO compliant). I used the following pre-order hook to fix the issue on one table:

DataTable.use(DataTablesCore);

const tableData = computed(() => {
  return props.datatable.pagination.data;
});

const tableColumns = [
  { data: "name", title: "Category", type: "case-insensitive" },
  {
    data: "description",
    title: "Description",
    type: "case-insensitive",
    render: (data) => {
      return limitLength(data);
    },
  },
  { data: "created_at", title: "Created On", type: "custom-date" },
  { data: "id", name: "edit", orderable: false },
];

$.fn.dataTable.ext.type.order["case-insensitive-pre"] = function (data) {
  return data?.toString().toLowerCase() || "";
};

$.fn.dataTable.ext.type.order["custom-date-pre"] = function (data) {
  console.log("Processing data in pre:", data);
  const format = "d LLL yyyy";

  const date = DateTime.fromFormat(data, format);

  if (!date.isValid) {
    console.error("Invalid date:", data);
    return 0;
  }

  return date.toISODate();
};

With the actual datatables component being:

                <DataTable
                  :options="{
                    pageLength: 50,
                    bPaginate: false,
                    bLengthChange: false,
                    bFilter: true,
                    bInfo: false,
                    bAutoWidth: true,
                    deferRender: true,
                    searching: false,
                    order: [[0, 'asc']],
                    typeDetect: false,
                  }"
                  :data="tableData"
                  :columns="tableColumns"
                  class="display pb-5 pl-5 pr-5 text-[10px]"
                >
                  <template #column-edit="props">
                    <div class="w-4">
                      <EditButton
                        @click="
                          updateCategory(props.rowData);
                          modalOpen = true;
                        "
                      />
                    </div>
                  </template>
                </DataTable>

This works perfectly! So I went to implement it into some of the other table components and found that it didn't function at all (the logging statement wasn't even being called when ordering), despite the fact that the case-insensitive-pre function does. After much experimentation, I found that reducing the number of columns to somewhere in the region of 3-4 meant that the custom-date-pre function worked correctly, but didn't when the intended number of columns were present. Code below for reference:

DataTable.use(DataTablesCore);

const tableData = computed(() => {
  return props.datatable.pagination.data;
});
const tableColumns = computed(() => {
  const columnArray = [];

  //columnArray.push({ data: "id", orderable: false, name: "checkbox" });
  columnArray.push({ data: "name", title: "Name", type: "case-insensitive" });
  columnArray.push({ data: "email", title: "email", type: "case-insensitive" });
  columnArray.push({ data: "role_labels", title: "Role" });
  columnArray.push({
    data: "created_at",
    title: "Created On",
    type: "custom-date",
    className: "hidden md:table-cell",
  });
  columnArray.push({
    data: "updated_at",
    title: "Last Login",
    type: "custom-date",
    className: "hidden md:table-cell",
  });
  if (emailEnabled) {
    columnArray.push({
      data: "email_verified_at",
      title: "Email Verified On",
      type: "custom-date",
      defaultContent: "Email Not Verified",
      className: "hidden md:table-cell",
    });
  }
  columnArray.push({ data: "id", orderable: false, name: "edit" });

  return columnArray;
});

$.fn.dataTable.ext.type.order["case-insensitive-pre"] = function (data) {
  return data?.toString().toLowerCase() || "";
};

$.fn.dataTable.ext.type.order["custom-date-pre"] = function (data) {
  console.log("Processing data in pre:", data);
  const format = "d LLL yyyy";

  const date = DateTime.fromFormat(data, format);

  if (!date.isValid) {
    console.error("Invalid date:", data);
    return 0;
  }

  return date.toISODate();
};

and the component itself:

            <DataTable
              :options="{
                pageLength: 50,
                bPaginate: false,
                bLengthChange: false,
                bFilter: false,
                bInfo: false,
                bAutoWidth: true,
                deferRender: true,
                searching: false,
                bSort: false,
                ordering: true,
                order: [],
                typeDetect: false,
              }"
              :data="tableData"
              :columns="tableColumns"
              class="display text-[10px]"
            >
              <template #column-checkbox="props">
                <input
                  :id="`user_select_${props.cellData}`"
                  type="checkbox"
                  class="mr-2 inline-block h-4 w-4"
                  :value="props.cellData"
                />
              </template>
              <template #column-edit="props">
                <div class="w-4">
                  <EditButton @click="openModal(props.rowData)" />
                </div>
              </template>
            </DataTable>

N.B: I did wonder if the columns definitions not being a static array in the second example made a difference and so have tried it with them statically defined which did nothing... also, as mentioned before the case-insensitive-pre works perfectly well!

Also, I'm using Luxon for the date format conversions!

kthorngren

My guess is that not all the data in that column matches the format "d LLL yyyy. You can use settings() to see what type is set for. Use this example to execute this:

$('#example').DataTable().settings()[0].aoColumns;

Look at the sType for array element 4. It's set to "datetime-d MMM yyyy". If your column is set to string instead of a date format then not all the data in the column is matching the defined format.

We will need to see the issue to help debug. Please post a link to your page or test case replicating the issue.
https://datatables.net/manual/tech-notes/10#How-to-provide-a-test-case

Kevin

Clusterfaff

Thank you for your response!

Currently struggling with getting a jsfiddle or Vue SFC playground instance of this running as I can't seem to find a functioning CDN link for the vue3 datatables wrapper.

The sType for the array element you mentioned is null, as it is for all of the columns barring the first 2. I have disabled typeDetect in the options on the datatable (as there was some unwanted data justification occurring within some cells as a result). The first two (correctly) have the case-insensitive type applied as defined in my column definitions, but for some reason the ones with "custom-date" don't have it applied unless I reduce the number of columns down.

As a further source of frustration, I even tried changing how the api delivers the dates to an ISO format and then using a render() function in my column definitions to display it in the desired format. My understanding is that datatables should order based on the underlying data, not what is displayed in the cell, but this is not what is happening. When I remove the render function, the raw ISO dates can be correctly ordered, when I apply the render function it goes back to ordering improperly.

allan

For Vue, the easiest option is to take one on of my demos such as this one and fork it, then modify as you need to show the options.

That it doesn't work on a large data set, but does one a smaller one, strongly suggests to me, just as Kevin mentioned, that there is something in the data set that is causing the auto detect to reject it.

What you could try is using columns().types() to see what the detected types are on the large data set.

table.ready(() => {
  console.log(table.columns().types().toArray());
});

will dump that info to console.

My understanding is that datatables should order based on the underlying data, not what is displayed in the cell, but this is not what is happening.

Depending on what renderer you are using! See for example here. What rendering function are you using?

Personally I like ISO8601 over the wire, and then using a rendering function like that, so DataTables can display a locale suitable format for the end user (although it is possible to also specify a format if preferred).

Allan

Clusterfaff

So I have gotten the simplified version of the table hosted here: https://stackblitz.com/edit/datatables-net-vue3-simple-hjeskybf?file=src%2FApp.vue

and it works perfectly, of course! I have had to use a simplified version as in reality, the component looks as follows:

https://pastebin.com/4kqdCpE3

And I can confirm that it still doesn't function as intended in my native component.

It isn't even that I am changing the size of the dataset. All the tests I have done have been on an array of max 25 entries. The same data is being delivered, I am simply changing how many columns of data are being displayed by commenting out some of them. With the intended 7 columns, the custom-date type (and therefore the associated pre function) isn't applied, but the case-insensitive type and associated pre is.

The render function I tried with was:

    render: function (data) {
      const date = DateTime.fromISO(data);
      return date.toFormat("d LLL yyyy");
    },

On the relevant column definitions.

However, on your advice I tried:

render: DataTable.render.date("d LLL yyyy"),

and the page throws:

app-IpBsV3gr.js:23 TypeError: Cannot read properties of undefined (reading 'date')

Thank you for the help thus far!

Clusterfaff

Oh and just to add, this exact solution to the ordering issue works in another component of the app which is very similar:

https://pastebin.com/iwihgcbQ

kthorngren

Your posted test case doesn't sort the dates correctly. I'm not familiar with VUE3 so I moved the relevant code into this test case:
https://live.datatables.net/zetopuni/1/edit

There are two tables. The first is using the ordering plugin from your test case. The table order is the same as your test case and is not correct. The console shows the column is set to the custom-date type.

The second table is based on this ordering luxon example. It simply sets the luxon datetime format of d LLL yyyy. This table is sorted correctly.

Kevin

Clusterfaff

Hi Kevin, thanks for the response!

I'm unsure what you mean when you say it isn't sorting correctly as it is working as far as I can see both on the stackblitz I made and on both instances of the table that you:

https://ibb.co/QJXLwS4

The syntax you used for the second table of

DataTable.datetime('d LLL yyyy');

when used in Vue3, gives the error:

TypeError: w.datetime is not a function

So no beuno on that front!

kthorngren

You are right, I was misreading (ignoring) the months - thought all were December Haven't had my coffee yet.

Both cases work!

It looks like you are using Datatables 2 so not sure why you are getting errors with either of these:

render: DataTable.render.date("d LLL yyyy"),

or

DataTable.datetime('d LLL yyyy');

The only suggestion I have is to make sure all the dates in the column match the Luxon format d LLL yyyy. If they don't all match then that could affect the sorting.

Kevin

Clusterfaff

No worries - nothing I haven't done before!

Not entirely sure why either as I can't see any Vue specific documentation that would suggest the syntax would be different (and it also works in other components that contain datatables).

I can definitely confirm the data is all coming through in the luxon format as all the dates are serialised to that format by the laravel back-end before they arrive, so they are either a date in that format or null (in which case I use defaultContent to populate it with something else where relevant)

As an aside changing the render option to:

render: DataTablesCore.render.date("d LLL yyyy"),

Doesn't throw an error... but it also doesn't do anything!

kthorngren

Ah, I think the problem is with the return 0; for invalid dates. I believe Datatbles supports empty strings and null in datetime columns but 0 is an invalid date. Compare the Created column to the Updated column. Both have the same data but Updated uses return ''; instead.

https://live.datatables.net/zetopuni/2/edit

Kevin

Clusterfaff

I also had that idea and have since tried changing it to a blank string, as well as a hardcoded date from the 90s in case that was the issue, but no joy!

I'm not sure what's causing it but the issue seems to be that it just isn't recognising the type of registering for the column with:

type: "custom-date",

and therefore isn't applying the "custom-date-pre" function to it when ordering. The logging statements aren't being called at all. Doubly strange as the case-insensitive type I am applying to other columns is being recognised entirely fine and, consequently, is working!

I have had a breakthrough where when I declared type: "string" for columns that didn't have a type manually defined by myself, my custom ordering began working on 2 more table components. I'm not sure if that is intended or a bug or is intended when you disable typeDetect but then start declaring types on some columns but not others.

However, I do still have one table component that isn't behaving itself despite being outwardly the same as any of the others!

kthorngren

The only reason I can think of for the plugin to not run is if its initialized after Datatables is initialized.

Also make sure you are loading the Datatables library once. If multiple times the plugin may be applied to the wrong loaded library.

Kevin

Clusterfaff

Solved it, crushed it, smashed it. Turns out the one remaining table wasn't working because I had missed putting a type on one of the column definitions.

So there you go, if any future poor soul ends up disabling typeDetect to get rid of unwanted styling and then adding their own custom types to some of the columns for ordering purposes, for the love of god, declare types on all of your columns, not just the ones you care about. Not sure why it throws a wobbler if you don't, I'm just glad to have solved it!

Thanks for all your help Kevin, you've been a valuable rubber duck!

rf1234

I have an older version of Data Tables but the situation is quite similar (just using the ultimate date/time sorting plugin which is no longer required).

I make sure the code regarding date recognition and ordering is positioned AFTER the code that is required to deal with ordering of formatted numbers to make sure date columns don't accidentally are assigned "formatted-num" and are ordered like them.

Running that code BEFORE formatted-number ordering, in my case was the cause of non-recognition and wrong ordering of some date / time columns because those columns were later labeled to be "formatted-num" which was wrong.

Here is my code just for illustration. (Yes, wrote it long ago ... not very elegant but working):

var cur = [ 'EUR', 'CHF' ];

//auto detection only works if ALL cells of a column comply with the criterion !!!
//fields must contain an ISO currency code or space to be detected as formatted-numbers
//since all table fields with ISO codes always contain a number this works.
//in addition German number fields should be detected as formatted-num regardless of
//whether they contain an ISO code or not
$.fn.dataTable.ext.type.detect.unshift( function ( data ) {
//        if (typeof data !== 'undefined' && data !== null) {
//            if ( isNaN(data) ) {
    if (typeof data !== 'undefined') {
        if ( data != null )  {
            var i=0;
            while ( cur[i] ) {
                if ( isNaN(data) ) {  //search may only be performed on non-numeric fields
                    if ( data.search( cur[i] ) > -1 )   {
                        return 'formatted-num';
                    }
                } else { //for pure numbers we want the same sorting as well!!
                    return 'formatted-num';
                }
                i++;
            }
            if ( data === '') {
                return 'formatted-num';
            }                
            if (lang == 'de') {
//we are not using strict mode in moment.js so that 28.12.2017 09:42 is also
//recognized as a date!!
                if ( ! moment(data, 'L').isValid() ) {
                    if ( isNaN(data) ) {
                        data = data.replace( /[\.]/g, "" );
                        data = data.replace( /[\,]/g, "." );
                        if ( ! isNaN(data) ) {
                            return 'formatted-num';
                        }
                    }
                }
            }                    
        }
    }
    return null;
} );

//sorting of formatted numbers in English and German format
$.extend( $.fn.dataTable.ext.type.order, { 
    "formatted-num-pre": function ( a ) {
        if (lang == 'de') {
            a = a.toString().replace( /[\.]/g, "" );
            a = a.toString().replace( /[\,]/g, "." );
        } else {
            a = a.toString().replace( /[\,]/g, "" );
        }
        a = a.toString().replace( /[^\d.-]/g, "" );
        a = parseFloat(a);
        if ( ! isNaN(a) ) {
            return a;
        } else {
//14 digit negative number to make sure empty cells always stay at the bottom / top
            return -99999999999999;
        }
    },
    "formatted-num-asc": function ( a, b ) {
            return a - b;
    },
    "formatted-num-desc": function ( a, b ) {
            return b - a;
    }
} );

//should be positioned after the number formatting to make sure
//date columns don't accidentally are assigned formatted-num
//set parms for date sorting using moment.js
if (lang === 'de') {
    var momentLocale = 'de';
} else {
    var moment.locale('en-gb');
}
// https://datatables.net/blog/2014-12-18  Ultimate date/time sorting
$.fn.dataTable.moment( 'L', momentLocale ); 

kthorngren

Turns out the one remaining table wasn't working because I had missed putting a type on one of the column definitions.

I 've never used typeDetect. I guess it makes sense that turning it off results in a null type assigned to all columns not using columns.type. This can be seen here:
https://live.datatables.net/jipanope/1/edit

Sorting the Updated column doesn't use the plugin.

Glad its all sorted out! EDIT: No pun intended

Kevin