jquery Datatables - how to achieve Server side fetching and Client side sorting

Question

I am using the datatables plugin for processing our tables. We have this use case where we need to fetch the data (paginated)through an ajax call using bServerSide and sAjaxSource but would like to just sort the data on that page rather than fetching the sorted data from the server, as in our case this read would be very expensive.

Is it possible to do it? If yes, please suggest how to proceed. Thanks in advance!!

did you ever figure this problem out? Need the same solution!! — SpoiledTechie.com
You have unique requirement, I am looking for server side custom sorting on columns like currency, time duration etc. — Riz

Nicola Peluchetti Nicola Peluchetti · Accepted Answer · 2011-06-07T09:01:35

I've read this discussion that might help you: basically you must inizialize de table with these options;

"bServerSide" : false,
"sAjaxSource" : "path to your ajax source"

In this way, the data get loaded only once and all the filtering is done by the client.

Than if you want to load more data you can use fnReloadAjax. You can read this discussion about the topic, i think it has all the answers you need.

EDIT - With pipeline you can avoid making frequent calls to the server for pagination only (if you filter data a call is made to the server). If you want to get the data to the server only once and then filter data clientside you must disable server side processing (white the above options set). If you disable server-side processing you can provide the user a way to get additional data from the server with "fnReloadAjax" (for example a button).

What i still don't get, do you need to get some other data from the server or whatever the user gets first is ok?

EDIT 2 - if you don't wan't to call the server, you can avoid using AJAX at all by enhancing an existing table. Just create the html servers side like this:

 <table width="770" border="0" id='rdr_home' class='tablesorter'>
        <thead>  
          <tr>
            <th>col1</th>
            <th>col2</th>
            <th>col3</th>
          </tr>
        </thead>
        <tbody>
    <?php 
    foreach ($rows as $row){
    //echo all rows here (be careful as for each row you must 
    //have as many <td> as the column: no colspan!
    }
    ?>
        </tbody>
    </table>

And then create your datatable like this for example

oTable = $('.tablesorter').dataTable({
        "aaSorting": [[2, "asc"]],
        "bAutoWidth": false,
        "bFilter": false,
        "sDom": 'T<"clear">lfrtip',
        "aoColumns": [
                    { "sType": "html" },
                    { "sType": "html" },
                    null
                ],
        "oLanguage": {
            "sUrl": "templates/rhuk_milkyway/dataTables/media/language/it_IT.txt"
        }
    });

With this you have pagination and filtering server side

EDIT 3 - in the case you have mentioned (datatables takes care of paginetion but no calls for filtering) you must add som extra plugin, i think. You must disable filters and sorters of datatable at inizialization fase like this;

    "bFilter": false,
    "bSort": false,

And then use another component for sorting filtering what's on screen. You can take a look here for a solution with sorting and filtering: http://silverwareconsulting.com/index.cfm/2008/10/2/jquery-autofiltering-table

(personally i don't like the idea of filtering by clicking, but you can build on this if you want to use somethin more to your taste), in any case you can't use datatables built-in filters if you allow server side pagination

jquery Datatables - how to achieve Server side fetching and Client side sorting

2 Answers