Natural Sorting Not Working

janetcl

Hello, I am working on a price comparison table, and I have a few features that I would like to add.

1. I am sorting in descending order by the discount column, which is hidden (but the value is displayed in the CDO column). I initially tried to define the type for column 10 (the discount) as "natural-nohtml" using the natural sorting plug-in, but it does not sort properly. The sorting worked at one point when I defined the type of column 10 as "percent", but since I've changed the way the data is defined to be part of an object rather than a number, it no longer works. The "percent" type now sorts in descending order from "9 8 7 6 5 4 37 3 20 2 10 1", which is clearly not correct. The "Sort by our best deals" button is meant to sort by the discount in descending order, and right now it just does the non-natural sorting as seen above. How can I fix this issue with natural sorting and the percent sign?

2. I would like to implement natural sorting AND absolute sorting in the remaining columns, so that the default content "N/A" is always at the bottom, and the numbers are sorted naturally. Right now I am implementing natural-nohtml sorting on the columns, and "N/A" is regarded as being the greatest value, so by sorting in descending order, "N/A" is always at the top (when I want it at the bottom). How can I achieve this? Thanks in advance for the help.

Link to the site (I am based in Argentina, so the link may not work if you are not in the country):
http://precios.centraldeofertas.com.ar:4000/

janetcl

I've attached my code (it's too long to put in the question or paste directly here).

janetcl

Additionally, here is a screenshot of the page.

kthorngren

The link doesn't work for me. The attached file doesn't have any data in it so its hard to know what is in column 10. There is a lot of code in your page. Can you post a simple example with your data so we can help you sort it?
https://datatables.net/manual/tech-notes/10#How-to-provide-a-test-case

Kevin

janetcl

If you open the file index.html with a text editor, it has code in it. I'll reupload a cleaner version of the index.html file here. Additionally, here is a live test case. The issue is my data is loaded in from a request to mongoDB, so I can't physically add data in the table.

http://live.datatables.net/yogejoto/1/edit

janetcl

Here is a photo of the object that represents 1 product inside of the MongoDB collection. Please let me know if I can provide more information to make this clearer.

kthorngren

Sorry, I should have been more clear. Can you provide a working test case with an example of your data. Maybe take a few rows of your data, package it in a Javascript variable and apply it to the example using the columns.data option. Similar to this example:
https://datatables.net/examples/data_sources/js_array.html

While creating the example maybe you can reduce the code down to the essentials needed to help. All the formatting etc is not needed to help with sorting your data.

Kevin

janetcl

I think that the issue has to do with how the data is loaded in, so changing the formatting to be an array may not help. I tried to make it into the JS Array format, but now the data won't load into the table at all. Part of the reason why the code may not be working is that I am adding on a lot of extra elements such as yadcf filters, autocomplete searching, etc.

Please let me know how I can fix this test case example to make it workable. Thank you so much for your help.

http://live.datatables.net/fifokame/3/edit

janetcl

This is unrelated to the sorting issue, but I would like to be able to reload the table with altered data every time a column is hidden, since some of the data (the discount) is calculated based on the data present in the table. Currently I am using buttons and col.vis to alter the visibility of columns. I am also using a rowCallback function to color the cell with the lowest value, so if I hide a column, I need to reiterate through the row callback function to recolor the table. I know it is possible to use cells.every() or cells.data() and ajax.reload() to achieve this, but I am unsure of exactly how to do it. Thanks again.

kthorngren

Here is a start to a test case with your data:
http://live.datatables.net/nuquwoca/1/edit

I think the issue you are working is with column 10 (Discount)? Which has the value -0.14. Correct?

I hid columns 1-9 just for display purposes to see the column we are working with.

It would be good to have different data points for column 10 so we can have have a better sampling of the data you have. Please update your dataset with other numbers you may have in your data.

I added the CDN link for the Natural into the test case. Many of the CSS and JS include files you have are referenced locally so your example isn't loading them. Also I noticed you are loading a file called naturalSort.js. Is this the same plugin on this page?

https://datatables.net/plug-ins/sorting/natural

This is unrelated to the sorting issue, but I would like to be able to reload the table with altered data every time a column is hidden,

To keep from confusing this thread I would suggest starting a new thread for this topic.

Kevin

janetcl

Thanks, Kevin! Here's an updated version with differentiated values for the discount column to reveal the issue at hand. Yes, I am using the natural sort plug in.

http://live.datatables.net/nuquwoca/3/edit

kthorngren

When I sort the Discount column it looks correct to me. Maybe I'm missing something.

Kevin

janetcl

It's working for me too. This means the problem has to do with how I configured my file, and likely how I am loading the data in. In any case, is there a way to maintain both absolute and natural sorting?

Here I've added "N/A" as two values in the table, which I would like to remain on the bottom. The issue is that the discount column has both numbers and strings (N/A), so how do I use absolute ordering with natural sorting?

http://live.datatables.net/nuquwoca/4/edit

kthorngren

Unfortunately a column can only have one type. If you had only positive numbers we could use Orthogonal Data and prefix leading zeros to the numbers. This would allow them to sort properly with absolute sorting and not use the natural plugin. But the negative numbers throw a wrench into using leading zeros.

The only other option I can think of is to create your own sorting plugin or to modify the natural plugin to merge the two plugins.

Others may be able to provide other options.

Kevin

janetcl

This is very helpful, actually. I think I can work around the issue with the discount column, and now I am trying to use Orthogonal Data to sort the remaining columns both naturally and with absolute data so that "N/A" is always at the bottom. However, in this test case, the absolute sorting is not working. What am I missing here? The column of interest is column 4, or "Maxiconsumo."

The way that my data is loading in, if the price is unavailable, then the value is null -- it is not a string that says "N/A". Should I change the format so that the raw data is a string that says "N/A"? I tried both options in the test case (null and "N/A"), and neither one works. Thanks for the help.

http://live.datatables.net/nuquwoca/7/edit

kthorngren

You have this line:
"type": "dispType", "targets": [3, 4, 5, 6, 7, 8]}

dispType is a variable but you are using it as a string. Changing it to this works:
"type": dispType, "targets": [3, 4, 5, 6, 7, 8]}

Updated example:
http://live.datatables.net/rariwoti/1/edit

According to the Absolute Sorting blog you can pass an array of strings to the $.fn.dataTable.absoluteOrderNumber method. You might be able to use both "N/A" and null but not sure about the null.

Kevin

janetcl

Amazing, thank you so much! I got the absolute sorting to work on all of the seller columns. Now the only problem that remains is the natural sorting that still isn't working in the discount column.
Could it have to do with the fact that it is not visible? I am now trying to alter the value with render for column 10. Does render not work if the column is not set to be visible?

{ "render": function (data, type, row) {
                        if (type == 'display' || type == 'filter') {
                          return data;
                        }
                        var sortingValue = 1000 + data;
                        return sortingValue;
                      },
                      "type": "percent", 
                      "targets": 10
 },

kthorngren

Does render not work if the column is not set to be visible?

Good question, I don't think it will if hidden. But, I haven't tried. One thing you could do is put console.log(data); or whatever at various points in the render function to see how it behaves. Its not always intuitive as to when it runs and when it doesn't and which type it is used.

Kevin

janetcl

Thank you so much! The issue was that my values in the discount column were actually strings, so adding 1000 to the value just concatenated the strings. By casting the data to a number, the sorting works perfectly!