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

Unhair

So you only want to sort 1 page, not the entire result set? – Waneta

did you ever figure this problem out? Need the same solution!! – Mcneely

You have unique requirement, I am looking for server side custom sorting on columns like currency, time duration etc. – Streamlet

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

Flurried

So, I guess my use case should be serviced if I have set cache size as 1 in the pipelining example. – Unhair

i edited my answer. to use pipelining to avoid making calls to the server you should set "iPipe" to a very high value. – Flurried

So the use case is something like this - For the search criteria, the total result set could be 10K rows. Because its very large, we want to fetch it 100 rows at a time (pagination). But I want to allow the user to sort just these 100 rows at a time. So, pagination needs to be done server-side but not the sorting. Sorting just needs to be done on the current page. – Unhair

remember that pipelining only avoids calls to the server for pagination, not for filtering. – Flurried

is there anyway, we can avoid the calls for sorting as well. – Unhair

Have you ever thought about not using ajax at all?Just create an HTML table with whatever language you use and then enance it with datatables: in that case you havo no calls to the server! That's how i've been using datatables for teh first year: PHP server side for creating the table and then everything else done client side: be sure to format you html table properly for datatables to work i'll post an example after lunch! – Flurried

But in this example, we are not fetching next pages from server which is what the major use case is. Pagination needs to be done. – Unhair

Ok, now i get what you need. You must use additional plugins to achieve what you want i think – Flurried

Guys, I've tried doing this without Ajax on a DataTable with 5 million+ records. Javascript can't handle the reformatting of that table in a reasonable amount of time. Feel free to prove me wrong, but in my tests it wasn't pretty! – Peek

It worked after sorting using tablesorter plugin. See - #6269291 – Unhair

@Unhair Glad it finally worked! @Peek I had problems with a mere 1000 thousand rows client side! – Flurried

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.

Peek

Answer 3

Please refer below code will help you to get data from database without making datatable serverside:"true". this will be best way according to me instead of making datatable serverside and writing large sql query and applying filters separately.

$.ajax({
  type: "POST",
  url: '@Url.Action("AjaxMethodAsset", "DemoSet")',
  data: { /*parameters*/},
  dataType: "json",
  success: OnSuccess,
  failure: function(response) {
    alert(response.d);
  },
  error: function(response) {
    alert(response.d);
  }
});
function OnSuccess(response) {
  $("#tblasset").DataTable(
    {
      "destroy": true,
      'columnDefs': [
        //hide the second & fourth column
        {
          'visible': false,
          'targets': [2, 3, 4, 7, 8, 9, 10, 11, 12, 13, 14, 15]
        },    
      ],
      "order": [0, 'desc'],
      "iDisplayLength": 2,
      bLengthChange: false,
      lengthMenu: [[5, 10, -1], [5, 10, "All"]],
      bFilter: true,
      bSort: true,
      bPaginate: true,
      data: response,
      columns: [
        { 'data': 'AssetTagNo' },
        { 'data': 'ArticleNo' },
        //Other columns,   
      ]
    });
  };

Limonene