How to impose numerical sort with dataTables?

irina_ikonn · September 2023

I am using the DataTables jQuery plugin. Table sorts alphabetically and not numerically. I get:
1
10
2
3
4
5
6
7
8
9
When I add the same part of html code to the https://live.datatables.net/ I get correct sorting.

allan · September 2023

It means that for some reason DataTables has detected your column's data as having non-numeric data in it. We'd need to be able to see the data to understand which specific data point is causing that issue. Please either link to the page showing the issue or use the debugger to give me a trace please - click the Upload button and then let me know what the debug code is.

Allan

irina_ikonn · September 2023

<script type="text/javascript">
        $(document).ready(function(){
            $('table').DataTable({
                pageLength: 50,
                responsive: true,
                language: {
                    url: "{{url_for('static', filename=_('dataTable_language_url'))}}"
                }
            });
        });
</script>

I found the reason. If I delete: language: {url: "{{url_for('static', filename=_('dataTable_language_url'))}}"}
table sorts correctly.
But I need make translate table to other language and get sorting table by "id" column.
https://live.datatables.net/dovinaci/1/edit

irina_ikonn · September 2023

How can I make translate to other language and sort table by "id" column?

irina_ikonn · September 2023

I use debugger. After click on Upload button I get "404 - Page not found
Sorry, the debug trace you are looking for does not exist or is private".

allan · September 2023

Correct. As I noted "click the Upload button and then let me know what the debug code is". You get a 404 because only I can see the debug traces for data security.

Your example works just fine with a language file: https://live.datatables.net/dovinaci/3/edit .

Allan

kthorngren · September 2023

I updated your test case so that Datatables initializes the table by adding the id attribute. I added the language.url and sorting of the id column works. Can you update the test case to show the issue so we can help debug?

Updated test case:
https://live.datatables.net/dovinaci/2/edit

the debug trace you are looking for does not exist or is private

Are you trying to click on the unique code where it says Upload complete? Only the Datatables developers can access this link. You are to provide the unique code to Allan so he can open the debug image.

Kevin

irina_ikonn · September 2023

Unique code: https://debug.datatables.net/ijelaj
In this test case table does not sort correctly.

I found: https://stackoverflow.com/questions/69586008/datatables-sorts-numbers-without-spaces-and-strings
I added in the code:

    $.extend( jQuery.fn.dataTableExt.oSort, {
      "formatted_numbers-pre": function ( a ) {
        a = (a===" ") ? 0 : a.replace( /[^\d\-\.]/g, "" );
        return parseFloat( a );
      },
      "formatted_numbers-asc": function ( a, b ) {
        return a - b;
      },
      "formatted_numbers-desc": function ( a, b ) {
        return b - a;
      }
    });

 $(document).ready(function(){
            $('table').DataTable({
                pageLength: 50,
                "columnDefs": [
                 { "type": "formatted_numbers", "targets": '_all'},
                ],
                responsive: true,
                language: {
                    url: "{{url_for('static', filename=_('dataTable_language_url'))}}"
                }
            });
        });

After that table sorts correctly.
Is there a more elegant solution to the problem?

kthorngren · September 2023

The need to use a.replace( /[^\d\-\.]/g, "" ) indicates that there are nonnumeric characters in the id column. Do you have nonnumeric data in the id column?

I don't think its a good idea to apply the formatted_numbers plugin to all columns using { "type": "formatted_numbers", "targets": '_all'},. You should apply it to only those columns you want to remove nonnumeric characters from the numeric data. See the columnDefs.targets docs. I updated the example to show how using "targets": '_all' is a problem.
https://live.datatables.net/rewitewi/1/edit

I updated the last column to have one value with zdata. The above code removes all characters except digits, - and .. For this column the sorting data ends up being an empty string for the name column. Try sorting it to see.

Kevin

irina_ikonn · September 2023

Id column has only numbers.
In the test case https://live.datatables.net/rewitewi/1/edit I always get correct sorting. In my Flask app I get correct sorting only after delete: language: {url: "{{url_for('static', filename=_('dataTable_language_url'))}}"}.
When add "language" I get translated table and:
1
10
2
3
...
I translate to Russian language. In a browser in which Russian is not installed table sorts correctly as:
1
2
3
...
10

irina_ikonn · September 2023

As far as I understand after set language number in the id column as string

allan · September 2023

Without being able to see an example of it not working, it is really hard to debug. I can't fix an example which is already working correctly .

If you post a link to your page showing the issue I can take a look and see what is going wrong.

The debug trace shows that the second column (Ð¸Ð´ÐµÐ½Ñ‚Ð¸Ñ„Ð¸ÐºÐ°Ñ‚Ð¾Ñ€ is detected as numeric and should be number sorted.

Allan

irina_ikonn · September 2023

I can't post link to my web app.
I do:

            <script type="text/javascript">
                    jQuery.extend( jQuery.fn.dataTableExt.oSort, {
                        "formatted_numbers-pre": function ( a ) {
                        return parseFloat( a );
                      },
                        "formatted_numbers-asc": function ( a, b ) {
                        return a - b;
                      },
                        "formatted_numbers-desc": function ( a, b ) {
                        return b - a;
                      }
                    });
                    $(document).ready(function(){
                        $('table').DataTable({
                            pageLength: 50,
                            responsive: true,
                            "order": [[ 1, 'desc' ]],
                            "columnDefs": [
                            { "type": "formatted_numbers", "targets": [0, 1]},
                            ],
                            language: {
                                url: "{{url_for('static', filename=_('dataTable_language_url'))}}"
                            }
                        });
                    });
                </script>

This code solves my problem

allan · September 2023

I can only really guess you might be using an old version of DataTables then. 1.10+ support sorting of formatted numbers built in.

Allan

kthorngren · September 2023

Can you copy, all or part of, the generated HTML table and update your test case to see if the problem is replicated?

Kevin

irina_ikonn · September 2023

I am using DataTables 1.10.16.
Unfortunately, I can't repeat my problem when I insert all html code to the https://live.datatables.net/.
If I set German language instead of Russian I get the same problem in sorting.
I generate pandas dataframe which has id column with type - int64. Then I do: dataframe.to_html().

When I do: console.log(typof a) in the code (jQuery.extend( jQuery.fn.dataTableExt.oSort)
I get type a - string.

allan · September 2023

I don't know that we can offer any help if we can only see examples that work, and not one that is showing the issue. We'd need a link to a test case showing the problem to fully resolve it.

Allan

kthorngren · September 2023

Maybe try loading the data using ajax and returning the Pandas dataframe as a JSON string. Like this example.

Kevin

How to impose numerical sort with dataTables?

How to impose numerical sort with dataTables?

Answers

Howdy, Stranger!

Categories

DataTables

How to impose numerical sort with dataTables?

How to impose numerical sort with dataTables?

Answers

Howdy, Stranger!

Quick Links

Categories

DataTables