Does Datatables' natural sort plugin work where data is got with server side processing?

0
votes

I got it to work with static html data as shown in this jsfiddle:

http://jsfiddle.net/L7PNV/

by specifying 

aoColumns: {"sType": "natural"}

where when I sort the first column it shows rows in right (natural-sorted) order:

SIR_1_SIL_CGG_2011_224_SILAC1b_IMAC_Fr1    
SIR_2_SIL_CGG_2011_224_SILAC1b_IMAC_Fr1    
SIR_3_SIL_CGG_2011_224_SILAC1b_IMAC_Fr1    
SIR_10_SIL_CGG_2011_224_SILAC1b_IMAC_Fr1
...

But then I want to apply natural sort to this same datatable where data is fetched server-side, the code where I invoke the data table is the same as in the jsfiddle adding the serverside related things:

$ -> 
  $("#sir_table").dataTable
    sPaginationType: "full_numbers"
    bProcessing: true
    bServerSide: true
    aoColumns: [{"sType": "natural"}, {"sType": "natural"}, {"sType": "natural"} ]
    aaSorting: [[ 1, "asc" ]]
    sAjaxSource: $('#sir_table').data('source')

The DataTable shows alright but the natural-sort does not work as it should (like in the jsfiddle, as above). Instead rows are displayed:

SIR_1_SIL_CGG_2011_224_SILAC1b_IMAC_Fr1    
SIR_10_SIL_CGG_2011_224_SILAC1b_IMAC_Fr1
... 
SIR_19_SIL_CGG_2011_224_SILAC1b_IMAC_Fr1
SIR_2_SIL_CGG_2011_224_SILAC1b_IMAC_Fr1
SIR_20_SIL_CGG_2011_224_SILAC1b_IMAC_Fr1

Why is this happening? Is natural-sort plugin even compatible with server side processing? How should I implement it?

2
sortingdatatablesnatural-sort

2 Answers

1
votes

No, I would say, you can't use this plugin when doing serverside processing.

As soon as you retrieve the data from an external source dataTables will just show a window of the data thats on the server. This can be thousands of rows, and this is the whole idea behind serversided processing. dataTables will just send a POST or GET request with some information to the server and will be given back just the fraction of data that fits in the current view. Clamped by pagenumber and items_per_page.

Also sorting and filtering is done on server side. So dataTables sends an request to the server every time you sort a column, click on the pagination or apply a filter. You can (for example use firebugs console to have a look at this request. You will find the actual page number, the amount of rows/page and sorting info for each of the rows in form of ASC or DESC.

Lets say your server uses PHP and MYSQL Then php would take this data, fetches its field from the db with LIMIT pagenumber*acutal_page, rows_per_page and ORDER BY field, ASC/DESC.

So in this case the sorting would be done by MySQL (which is not very good for natural sorting, although it is possible).

If you use mongoDB and/or node.js as underlying datasource it should be fairly easy to implement the (very advanced and awesome) sorting algorithm from dataTables, since this is plain javascript which can be handled by both.

But this has to happen on the serverside, on the client side dataTables will just see a fraction of the vast amount of data that is on your server.

Hope this helps a bit.

1
votes

Just modify the 'static function order ( $request, $columns )' function in 'ssp.class.php':

if ( $column['db'] == 'Nr' ) {
    // Natural Sort
    $orderBy[] = 'LENGTH(`' . $column['db'] . '`) ' . $dir . ', ' . '`' . $column['db'] . '` ' . $dir;
} else {
    $orderBy[] = '`' . $column['db'] . '` ' . $dir;
}

From now on the column with the name 'Nr' will have natural sort.