data-sort breaks render function

alessio.moretti

Hi!
I have a datatable with some cells containing numbers. These numbers are formatted within a columnDefs.render function.
I have noticed that if I add a data-sort attribute to a cell the formatting done in the render function is lost, though the function is correctly executed.
Example https://jsfiddle.net/gfcm3tx0/
notice the missing data-sort on the last column, which is correctly shown. While the other two numeric columns contain a data-sort and the rendered value is somehow lost.

alessio.moretti

To explain more, I have a pre-populated html table with raw data like

    <tr>
      <td>Some value</td>
      <td>Foo</td>
      <td data-sort="34">34</td>
      <td data-sort="23.23423">23.23423</td>
      <td>54.63262</td>
    </tr>

and a rendering function that's supposed to format cell values, e.g.

columnDefs: [
    {
      targets: '_all',
      render: function (data, type, row, meta) {
        if (type === "sort" || type === 'type') {
           return data;
        }
        if (meta.col > 2) {
           return data + '$$';
        }
        return data;
      }
    }
  ]

The function is executed and its return value is correctly shown only in cells with no data-sort attribute (the last one).

kthorngren

I updated your first example to better illustrate the issue.
https://jsfiddle.net/4wmv2jk1/

It seems that if you have data-sort the display operation is not performed on that column. @allan or @colin will need to take a look to tell us why this happens and if its expected.

Kevin

alessio.moretti

Thank you for your answer. In a post I read it could be an issue related to the way datatable caches data before sorting, but I couldn't find a way to invalidate it or work around it.

alessio.moretti

Anybody?

allan

I hadn't really expected a renderer to be used in combination with the data-* attributes. I'd assumed that if you were using them, then it would already be formatted to your liking. So it looks like there is an issue around here, but I'm not yet certain what it is.

What is puzzling me is that the data given to the rendering function when you are using the data-* attributes is not an object. It should be as is in the row object. I'll need to look into this further.

Allan

alessio.moretti

All right thanks! Also, if you think of a workaround or a place where I could move my render function please let me know!

gyrocode

@alessio.moretti

I suggest to use the following modification:

1. While you're waiting for the fix, change your data-sort attribute into data-sort2 attribute or use any other name.
2. Use the following code in your rendering function:

    if (type === 'sort' || type === 'type') {
      var api = new $.fn.dataTable.Api(meta.settings);
      var $td = $(api.cell({row: meta.row, column: meta.col}).node());
      var sortData = $td.data('sort2');
      return (typeof sortData !== 'undefined') ? sortData : data;
    }

Please note that this modification most likely will affect rendering and sorting performance if you have large number of records and use client-side processing.

Alternatively you can add a new hidden column with sort data and return that column data in the clause above. It will be slightly faster but not as elegant.

See this example for code and demonstration. I corrected your rendering function to only apply to certain columns.

gyrocode

@alessio.moretti

However there no sense for you to use data-sort attribute at all because your sort data doesn't differ from the data in the <td></td> element.

See this updated example where I simplified your code and the result is the same.

---

See more articles about jQuery DataTables on gyrocode.com.

alessio.moretti

@gyrocode thanks a lot! The custom data-sort2 attribute solution works fine! Hopefully, the table is not large enough to have performance be affected.

However there no sense for you to use data-sort attribute at all because your sort data doesn't differ from the data in the <td></td> element.

Right, but in fact in my table cells I also have values that are not numbers and require a data-sort attribute, such as "N/A" or "-" strings (e.g. <td data-sort="-1">N/A</td>).

smiddick

@gyrocode, I appreciate your example, so thank you ! I spent 3 days trying to render the "data-sort" value on an Excel export. My 'display' for that column is a graphic so I didn't want that on the spreadsheet, I wanted descriptive text. Your example helped me come up with:
name: "status", render: function(data, type, row, meta) { var api = new $.fn.dataTable.Api(meta.settings); var $td = $(api.cell({ row: meta.row, column: meta.col }).node()); var sortData = $td.data('sort'); return (typeof sortData !== 'undefined') ? sortData : data; }
(I do have exportOptions: { columns: ':visible:not(.noexport)', orthogonal: "dataExport" } on my excel button, so maybe it "just works")

I don't quite understand why it works without me specifically checking for 'export', but it does. It displays the graphic on the webpage, and when I click on my Excel button, it renders the text associated with that graphic in the resulting spreadsheet. Any comments regarding this little puzzle are welcome. Thanks again!