Sorting by surname if first and lastname are in th

问题:

I have a very simple datatable setup with a few fields and a few rows of data. Column sorting has been set. I have a field which is basically "customer name" and in it goes both the first name and last. I want to know if there is a way to change the datatables sorting method to sort based on the surname (or last word) in the same column.

回答1:

While you are right about a custom sort function, and that's probably the way to go, I caution against splitting the name on a space, which will get you incorrect results in some cases.

DataTables allows you to attach arbitrary data to rows, and you can sort by that even though it's not displayed, so attach the actual last name separately.

Or, you can actually pass first and last name separately then use a custom renderer to paste them together to display in a single column.

回答2:

One other simple suggestion when a single column is needed for the First-Name Last-Name, but you want to sort on the last name: In WordPress or other php app, make the 'Name' column read as:

 <td>last-name first-name last-name</td>.  

The column, then, will sort by last-name.

Then, put the first instance of last-name as

 <span class="display-none">last-name</span>.

Is it a hack? Maybe, but a quick solution to the problem.

回答3:

If you wanted to handle this in the DB layer you would need to execute a Substring method in the orderby clause of your query, the syntax will vary depending on what DB you are using.

MSSQL:

ORDER BY SUBSTRING(COLUMN, CHARINDEX(' ', COLUMN), 50);

MYSQL:

ORDER BY Substring(COLUMN, Position(' ' IN COLUMN), 50);

回答4:

Update
First answer was a generic sort function, I somehow missed the datatables tag. Updated answer is for datatables.

Like @Stephen P said in comment you can configure datatables to sort on hidden column. So you can add hidden column with surname only and then set ordering of first column (full name) by second column (surname). I have used js to generate surname column from full name, but if you have lots of data you should do it server side.

$(document).ready(function() {
    // find full name, get surname and add it to table as second column
    $('#example td:first-child').each(function() {
      $('<td>'+$(this).text().split(' ')[1]+'</td>').insertAfter($(this));
    });
    // configure sorting
    $('#example').DataTable( {
        'columnDefs': [
          {'orderData':[1], 'targets': [0]},
          {
            'targets': [1],
            'visible': false,
            'searchable': false
          },
        ],
    } );
});

<script src="https://ajax.googleapis.com/ajax/libs/jquery/2.1.1/jquery.min.js"></script>
<script src="https://cdn.datatables.net/1.10.12/js/jquery.dataTables.min.js"></script>
<link rel="stylesheet" type="text/css" href="https://cdn.datatables.net/1.10.12/css/jquery.dataTables.min.css">

<table id="example" class="display" width="100%" cellspacing="0">
        <thead>
            <tr>
                <th>Name</th>
                <th>Surname</th>
                <th>Position</th>
                <th>Office</th>
                <th>Age</th>
                <th>Start date</th>
                <th>Salary</th>
            </tr>
        </thead>
        <tfoot>
            <tr>
                <th>Name</th>
                <th>Surname</th>
                <th>Position</th>
                <th>Office</th>
                <th>Age</th>
                <th>Start date</th>
                <th>Salary</th>
            </tr>
        </tfoot>
        <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>Airi Satou</td>
                <td>Accountant</td>
                <td>Tokyo</td>
                <td>33</td>
                <td>2008/11/28</td>
                <td>$162,700</td>
            </tr>
            <tr>
                <td>Brielle Williamson</td>
                <td>Integration Specialist</td>
                <td>New York</td>
                <td>61</td>
                <td>2012/12/02</td>
                <td>$372,000</td>
            </tr>
            <tr>
                <td>Herrod Chandler</td>
                <td>Sales Assistant</td>
                <td>San Francisco</td>
                <td>59</td>
                <td>2012/08/06</td>
                <td>$137,500</td>
            </tr>
        </tbody>
    </table>

回答5:

The way to do this these days is to simply attach a data-sort attribute to your <td>, e.g.:

<td data-sort="Johnson Dwayne">Dwayne "The Rock" Johnson</td>

Datatables will handle this automatically in the way you'd expect. For reference, see their documentation on HTML5 data-* attributes.