How can I disable the "Auto-detected columns type"?

Mat786786 · February 28

Link to test case:
Debugger code (debug.datatables.net):
Error messages shown:
Description of problem:

allan · February 28

You can use DataTable.type() and set the function for detect to return false - e.g.

DataTable.type('num', 'detect', () => false);

There is more discussion about type detection and handling available in the manual.

Allan

allan · February 28

Is that what you actually want to do, or do you just want to change a class name or something? You didn't exactly provide a lot of information about what you are trying to do

Allan

Mat786786 · February 28

Thanks for replying Allan, i'm using jQuery in my project, and i just want to disable by default the auto detect columns type

allan · February 28

Fair enough. My post above describes how to do that. You'd need to do it for each of the built in types.

Allan

Mat786786 · February 28

So if i understand correctly i need to replace this function.

DataTable.type('num', { className: 'dt-type-numeric', detect: function ( d, settings ) { var decimal = settings.oLanguage.sDecimal; return _isNumber( d, decimal ) ? 'num' : null; }, order: { pre: function (d, s) { var dp = s.oLanguage.sDecimal; return __numericReplace( d, dp ); } } });

to this in datatables.js file?

DataTable.type('num', 'detect', () => false);

allan · February 28

No, run the function I gave before you initialise DataTables. Don't modify the DataTables core file.

You'll need to run it for the other built in types as well, like I said.

Allan

Mat786786 · February 28

It's not working for me, here is my code.

DataTable.type('num', 'detect', () => false);
<script type="text/javascript" language="javascript"›
$(document). ready(function){
$(' table.datatable'). DataTable({
language:
"url": 'vendor/datatables/localisation/es_es.json'},
responsivé: true, fixedHeader: true, autoWidth: false, pagingType: 'simple', info: true, lengthMenu: [
[15, 50, 100, 300, 500, -1],
[15, 50, 100, 300, 500, "Todos"]
],
fixedHeader:
｝，
columnDefs: [
headerOffset: $('.navbar').outerHeight(),
targets: 'no-search', searchable: false,
targets: 'no-sort', orderable: false,
targets:
'sort_asc'
orderSequence: ['asc'],
targets:
'sort_desc'
orderSequence: ['desc'],
3);
3);
</script>

allan · February 28

I'm not sure why it is outside the script?

Mat786786 · February 28

Sorry my mistake while copying my code in forum.

I have like this in my file:

<script type="text/javascript" language="javascript"› $(document). ready(function){ DataTable.type('num', 'detect', () => false); $(' table.datatable'). DataTable({ language: "url": 'vendor/datatables/localisation/es_es.json'}, responsivé: true, fixedHeader: true, autoWidth: false, pagingType: 'simple', info: true, lengthMenu: [ [15, 50, 100, 300, 500, -1], [15, 50, 100, 300, 500, "Todos"] ], fixedHeader: ｝， columnDefs: [ headerOffset: $('.navbar').outerHeight(), targets: 'no-search', searchable: false, targets: 'no-sort', orderable: false, targets: 'sort_asc' orderSequence: ['asc'], targets: 'sort_desc' orderSequence: ['desc'], 3); 3); </script>

kthorngren · February 28

I built a test case and it doesn't seem to make the change:
https://live.datatables.net/sofuweve/1/edit

DataTable.type('num', 'detect', () => false);

console.log(DataTable.type('num'))

var table = new DataTable('#example');

Kevin

allan · February 28

That only removes the num type. The other types would need to be removed as well:

DataTable.types().forEach(type => {
  DataTable.type(type, 'detect', () => false);
});

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

I still have no idea why one want to do that though.

Allan

kthorngren · February 28

I the test case DataTable.type('num', 'detect', () => false); doesn't seem to remove the num type as the dt-type-numeric is still applied:

<tr>
            <td class="sorting_1">Angelica Ramos</td>
            <td>System Architect</td>
            <td>London</td>
            <td class="dt-type-numeric">36</td>
            <td class="dt-type-date">2009/10/09</td>
            <td class="dt-type-numeric">$2,875</td>
          </tr>

Possibly I'm missing something

Kevin

kthorngren · February 28

Nevermind, looking at it in more detail I see that the Age column can match any of these:

DataTable.type('num', 'detect', () => false);
DataTable.type('num-fmt', 'detect', () => false);
DataTable.type('html-num', 'detect', () => false);
DataTable.type('html-num-fmt', 'detect', () => false);

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

Once these are removed its no longer type detected.

Kevin

allan · February 28

Yup, I reused the class name for a few of them

Mat786786 · February 29

Hey allan,

This worked for me:

DataTable.types().forEach(type => {
     DataTable.type(type, 'detect', () => false);
});

But now the problem is that the sorting is not working correctly with numeric columns

allan · February 29

But now the problem is that the sorting is not working correctly with numeric columns

No, of course it isn't. Type detection has been disabled and DataTables won't detect numeric columns. It thinks everything is a string.

This is why I asked, back at the start, what it is that you are actually trying to do!

Could you clarify that please? Do you just want the numbers left aligned instead of default right aligned?

For that you could do:

DataTable.type('num', 'className', ' ');

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

Allan

jfade · May 16

Hi Allan!

I think I can elaborate a bit on the why, as I'm facing the exact same issue currently and trying to figure out how to work around it. In my case, we're using Server Side Processing and the SQL is doing the sorting. When returning the data, previously, Datatables would just respect the order that the SSP gave. Since the 2.0 update, it's now seeming to take the data returned from SSP and then apply this secondary sorting.

So in our case, we have the data stored for an appointment in the database in YYYY-MM-DD HH:II:SS format, and the SQL query handles the ordering based on the order parameter that Datatables sends via AJAX. The data is returned though with a formatter that not only formats it US style (m/d/Y h:i a) but also adds some additional HTML with additional information from other columns (IE red text saying CANCELED if applicable).

I'm only just now starting to debug the issue and maybe the code snippet you supplied above to remove the different sorting classes will work and solve the issue, but just to clarify a use case, with SSP, where the front end sorting in fact unsorts the data.

Perhaps there can instead be an option added to turn this off with a true/false parameter?

kthorngren · May 16

@jfade

Since the 2.0 update, it's now seeming to take the data returned from SSP and then apply this secondary sorting.

None of the client side processes are enabled when using server side processing. Take a look at this example. Use the browser's network inspector tool. You will see that the table is displayed in the order of the JSON response. You should see this too in your site. If not then please provide a link to a test case showing the issue.
https://datatables.net/manual/tech-notes/10#How-to-provide-a-test-case

If you are unable to provide a test case then post the JSON response and a screenshot of the table showing the difference in sorting.

The data is returned though with a formatter that not only formats it US style (m/d/Y h:i a) but also adds some additional HTML with additional information from other columns (IE red text saying CANCELED if applicable).

Is this done client or server side?

Kevin

jfade · May 16

Hi Kevin,

Everything is server side. And because the data is returned differently in every case (every filter, every client's data being different) this bug is being exceedingly difficult to find consistently. I'll try to locate a consistent case and provide you with an example.

But I know for sure in one instance: I have a simple table in a modal popup with name, address, and a view button that's added in client side with the columnDefs. The Address field most definitely has a dt-type-numeric class added to it. This right aligns the header which isn't desired. I ASSUME it's doing some other sorting as well, but maybe it isn't, and it's just the styling applied. But this is new behavior that it never did before.

Thus, I may be wrong in my conclusion in blaming the client side for this, but I updated 7 sites to use Datatables 2.0 and after the update these sorting issues appeared intermittently in them, none of the server side logic was modified at all, I only updated the JS libs.

One last comment: I suspect this is applied based on the first pull of data, and then is not applied with subsequent draws. The address example, on some pages it's only text or entirely blank, and it's still applying the numeric sort class. But in another copy of the exact same tool, this numeric class is never applied at all. The only difference is the data returned.

jfade · May 16

OK, still not having the incorrect sort issue on this other project, but if you go here: https://demo.donorwrangler.com/donations.php

Click around on the headers to sort them, and at some stage you'll see the numeric class suddenly applied to the Date and Amount fields. The header will align right. In this example it doesn't mess with the sorting, the data is cleaner and not with a bunch of extra HTML, but if this class is being added from server side data wouldn't it be logical that it starts applying sorting rules too?

kthorngren · May 16

I get access denied clicking your test case link. But its probably not needed unless you can show the incorrect sorting issue.

if this class is being added from server side data wouldn't it be logical that it starts applying sorting rules too?

The class is applied to the column when its sorted by Datatables type detection. See the types docs for details. Here is a simple example. Open the browser's console. Sort the Salary column and you will see the type detection taking place. That's when the dt-type-numeric class is applied to the th and the formatting changes.
https://live.datatables.net/yaroyala/15/edit

This is only the type detection process which is separate from sorting. Client side sorting will not happen when server side processing is enabled.

There is some inconsistency in that the class is only applied to the header and not the cells in the column. With client side processing the class is also applied to the table cells. See this example.

@allan can comment but I suspect the class shouldn't be applied at all with server side processing.

As a work around you can try either of Allan's suggestions above. I used the code to turn off detection:
https://live.datatables.net/xamafiwo/1/edit

Kevin

jfade · May 18

@kthorngren The demo is public, just click the login link and you'll be given the username/password to use.

I guess I'll just use the snippet to turn it off and see if it affects the other system (which is on an intranet so I can't link to it) and fixes the sorting issue. If so I'll try to get back to you with more info.

allan · May 20

If I click on your "Campaign" header, than the "Date" header will immediately switch to the right hand side with the dt-type-date class applied to it.

What is happening is that the type detection is re-running as the new data comes in, and it is finding that column to be a date type, so it applies the class for the autodetected type.

That's not really ideal for server-side processing. I need to have a bit of a think about what the right thing to do is here. I suspect Kevin's initial thought that the class shouldn't be applied with server-side processing is going to be the correct solution. Type detection is only really useful with the full data set.

Allan

allan · June 13

Quick update on this - I've committed a change to the 2.1 development branch for DataTables to disable automatic type detection when server-side processing is enabled.

Allan

Howdy_McGee · September 9

@allan Did anything ever come of this commit? I'm running into similar issue where my datatable pulls specifically from server side. I'm not looking for type detection, any sorting will happen on the server side and the results given back to DT for display.

Any link to track this kind of update?

allan · September 9

Yes - 2.1.0-2.1.5 had type detection for server-side processing disabled. That in turn created a host of issues such as SearchBuilder not showing the correct data type options and so on.

So in 2.1.6 which I released on Friday I re-enabled server-side processing client-side type detection, doing so as the data was loaded in rather than waiting for an action on that column (which was part of the issue before).

However! At the same time I committed a new detectType option which lets you disable auto type detection.

Unfortunately, I haven't yet pushed the docs for that new option to the site yet (I'll do that soon), but it is available here in its raw form.

Allan

jlh · October 31

If anyone else is looking for the new option detectType mentioned above, it is actually called typeDetect. The name is fixed in the source, but not yet in the live documentation.

In case anyone cares about my reasons for not wanting type detection:

Fixed-width ISO dates aligned to the right look really bad. Of course that's just my opinion.
Strings like "2024 – August", "2024 – September", etc. look MUCH better when aligned to the left, but DataTables now aligns them to the right.
Number detection is sometimes unwanted: I have short alphanumeric strings and I don't want them to be randomly align left or right when a short table happens to only have IDs that are also valid integers.
It breaks custom styling, so I have to first go through all tables in a big application, before I can even consider using it.

So, thank you for adding an easy opt-out!

How can I disable the "Auto-detected columns type"?

How can I disable the "Auto-detected columns type"?

Answers

Howdy, Stranger!

Categories

DataTables

How can I disable the "Auto-detected columns type"?

How can I disable the "Auto-detected columns type"?

Answers

Howdy, Stranger!

Quick Links

Categories

DataTables