7

So the question has already been asked here, but the solution doesn't work for me (I might do something wrong). I want to sort my tables by alphabetical order ("type" : "natural"), but I want the empty cells to be at the bottom (for desc and asc).

I tried the previous solution given by fbas :

jQuery.fn.dataTableExt.oSort['mystring-asc'] = function(x,y) {
    var retVal;
    x = $.trim(x);
    y = $.trim(y);

    if (x==y) retVal= 0;
    else if (x == "" || x == " ") retVal=  1;
    else if (y == "" || y == " ") retVal=  -1;
    else if (x > y) retVal=  1;
    else retVal = -1;  // <- this was missing in version 1

    return retVal;
}
jQuery.fn.dataTableExt.oSort['mystring-desc'] = function(y,x) {
    var retVal;
    x = $.trim(x);
    y = $.trim(y);

    if (x==y) retVal= 0; 
    else if (x == "" || x == "&nbsp;") retVal=  -1;
    else if (y == "" || y == "&nbsp;") retVal=  1;
    else if (x > y) retVal=  1;
    else retVal = -1; // <- this was missing in version 1

    return retVal;
 }

With :

$(document).ready(function() {
    $('#classement').dataTable({
    "aoColumns": [
        null,
        null,
        { "type" : "mystring" },
        { "type" : "mystring" },
        null
    ]
    } );
} );

With a table like | N° | Edit | Song | Singer | Url | Sorting only on Song and Singer.

The emty cells are at the bottom (as expected) but now the sorting has no logic (no alphabetical order, should I use another property in dataTable?).

Does anyone have a solution?

Edit : If we add a line dynamically, how to refresh the sorting ?

$("#example").find('tbody')
    .append($('<tr>')
        .append($('<td>')
                .text('Boro')
            )     
    );

JsFiddle (use isim's one)

Fanch
  • 3,274
  • 3
  • 20
  • 51

3 Answers3

19

UPDATE: Embedded Stack Snippet.

I think the aoColumns is a legacy option for DataTables v 1.9. That being said, you might also need to use $.extend to include your custom sort functions.

Please take a look at the Stack Snippet below, or this live demo on jsfiddle. In a nutshell, I define the name column as the type non-empty-string during the table initalization. Then I extended the jQuery.fn.dataTableExt.oSort API with a non-empty-string-asc and a non-empty-string-desc sorting functions. See if this is what you are looking for.

Stack Snippet:

jQuery.extend( jQuery.fn.dataTableExt.oSort, {
    "non-empty-string-asc": function (str1, str2) {
        if(str1 == "")
            return 1;
        if(str2 == "")
            return -1;
        return ((str1 < str2) ? -1 : ((str1 > str2) ? 1 : 0));
    },
 
    "non-empty-string-desc": function (str1, str2) {
        if(str1 == "")
            return 1;
        if(str2 == "")
            return -1;
        return ((str1 < str2) ? 1 : ((str1 > str2) ? -1 : 0));
    }
} );


var dataTable = $('#example').dataTable({
    columnDefs: [
       {type: 'non-empty-string', targets: 0} // define 'name' column as non-empty-string type
    ]
});
dataTable.api().row.add(['John Smith', 'Intern', 'San Francisco', 19, 2011/05/25, 62000]).draw();
<script src="https://ajax.googleapis.com/ajax/libs/jquery/2.1.1/jquery.min.js"></script>
<script src="http://cdn.datatables.net/1.10.2/js/jquery.dataTables.min.js"></script>
<link href="http://cdn.datatables.net/1.10.2/css/jquery.dataTables.css" rel="stylesheet"/>

<table id="example" class="display" cellspacing="0" width="100%">
    <thead>
        <tr>
            <th>Name</th>
            <th>Position</th>
            <th>Office</th>
            <th>Age</th>
            <th>Start date</th>
            <th>Salary</th>
        </tr>
    </thead>
    <tbody>
        <tr>
            <td>Tiger Nixon</td>
            <td>System Architect</td>
            <td>Edinburgh</td>
            <td>61</td>
            <td>2011/04/25</td>
            <td>$320,800</td>
        </tr>
        <tr>
            <td>Garrett Winters</td>
            <td>Accountant</td>
            <td>Tokyo</td>
            <td>63</td>
            <td>2011/07/25</td>
            <td>$170,750</td>
        </tr>
        <tr>
            <td>Ashton Cox</td>
            <td>Junior Technical Author</td>
            <td>San Francisco</td>
            <td>66</td>
            <td>2009/01/12</td>
            <td>$86,000</td>
        </tr>
        <tr>
            <td>Cedric Kelly</td>
            <td>Senior Javascript Developer</td>
            <td>Edinburgh</td>
            <td>22</td>
            <td>2012/03/29</td>
            <td>$433,060</td>
        </tr>
        
        <tr>
            <td></td>
            <td>Junior Technical Author</td>
            <td>San Francisco</td>
            <td>66</td>
            <td>2009/01/12</td>
            <td>$86,000</td>
        </tr>
        <tr>
            <td></td>
            <td>Senior Javascript Developer</td>
            <td>Edinburgh</td>
            <td>22</td>
            <td>2012/03/29</td>
            <td>$433,060</td>
        </tr>
        
    </tbody>
</table>
ivan.sim
  • 8,972
  • 8
  • 47
  • 63
  • Thank you, in fact it was working, it's just upper case and lower case are not managed by default. Your example gave me the clue. Another question, if I dynamically add a new line in the example, it seems this line isn't directly handled by sorting. See http://jsfiddle.net/Fanch/cbL9r757/ – Fanch Sep 20 '14 at 14:55
  • 1
    @Fanch You should insert the new rows using the [`row().add()`](https://datatables.net/reference/api/row.add()) API, follow by invoking the [`draw()`](https://datatables.net/reference/api/draw()) API. Take a look at this [fiddle](http://jsfiddle.net/ivan_sim/1toczk3f/). – ivan.sim Sep 20 '14 at 18:07
  • I'm using this code and it works great, but I'm wondering if theres a way to tell datatables to treat a with a specific class as if it were empty. I have that have links to add that info that is missing but of course datatables doesn't view it as empty. And i can't wrap a in an tag. – Tiffany Israel Apr 11 '17 at 15:16
  • How would this work for a column with integers in? How would you incorporate the num type? – pee2pee Jun 20 '18 at 16:25
  • Using this the Secondary Column Sort for Number is not coming in proper order. For Example, For the Null Values in the Primary Field that are Displayed in the Descding, the Amount Field in the Secondary Field will display the values in the Descending order. However, I want the Amount Column still be in Ascending Order when the counterpart Primary Column is Null and in descending order. What should I do? – iLearn Feb 11 '19 at 17:13
  • Note that you can also use `data-`attributes to control the type. So, in `` define eg `Name`. IMHO this ist better readable. – olidem Jan 21 '22 at 14:28
1

To ignore empty rows

Include https://cdn.datatables.net/plug-ins/1.10.25/sorting/absolute.js

var nameType = $.fn.dataTable.absoluteOrder( {
            // value can be a string or empty
            value: '',
            // postion top or bottom 
            position: 'bottom' // or top
        } );
         
        
$('#mytable').DataTable({
            columnDefs: [
                { targets: 0, type: nameType }
            ]
        });
ufopilot
  • 3,269
  • 2
  • 10
  • 12
0

Ok I found a solution for my second problem , see Here

So destroy the dataTable just before my ajax request and rebuild it on success :

    else{
        // Destroy dataTable
        $('#classement').dataTable().fnDestroy();
        $.ajax({
            type: "POST",
            url: "ajax.php",
            data: {},
            success: function(msg){
                // Reload dataTable with sorting
                $('#classement').dataTable({
                    columnDefs: [
                       {type: 'non-empty-string', targets: [2,3]} // define 'name' column as non-empty-string type
                    ]
                });
            }
        });
    }

Example : JsFiddle

Community
  • 1
  • 1
Fanch
  • 3,274
  • 3
  • 20
  • 51
  • 1
    You should insert the new rows using the [`row().add()`](https://datatables.net/reference/api/row.add()) API, follow by invoking the [`draw()`](https://datatables.net/reference/api/draw()) API. Take a look at this [fiddle](http://jsfiddle.net/ivan_sim/1toczk3f/). – ivan.sim Sep 20 '14 at 18:06
  • Thank you, useful infos :) I can't use `row.add()` because I set the table in the initialisation with empty cells, and then the user update them. Maybe I could use `row.indexes()` with a function which updates the specified row. – Fanch Sep 20 '14 at 23:46