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!!

Answer 1

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

Answer 2

I've got something nearly identical running, and the solution was pipelining all the way--nothing else magical and no need to combine other solutions. Set the pipeline volume high -- in my case I'm grabbing essentially 5 "groups" of results (pages * display results) both forward and backward -- and trust that Datatables will do the rest.

In one instance of my app, I'm dealing with 5 million+ records. Yes, it's a lot. I've done a ton of testing on the various elements of the system, including the queries, the database optimization, indexing, and UI pattern usage. All point to this system being the most efficient possible for the situation. For your case, I'd strongly reccomend some performance monitoring regardless of your UI decision to see what (if any) bottlenecks exist. In my 5 million+ record system, I'm looking at about 2-4 seconds of query time and +-5% load per page or sort, which is certainly manageable. I've got hundreds of users and quite a few other processes to deal with as well simultaneously, and we're seeing no noticeable lag since implementing multiple DataTables about 6 months ago.

From a UI standpoint, the weakness of sorting only the on-screen data is that it's not intuitive. When I've got access to thousands or millions of records on a grid and I sort or filter it, I expect to see the sort and filter of all the available records. It sounds silly, but remember that the science of UI design is all about creating patterns that are "familiar" to the user.