Pagination not working when using server side (ajax) processing without sending 'recordsTotal'

Sibin

I'm using DataTables 1.10.5. My table uses server side processing via ajax.

        $('#' + id).dataTable({
          processing: true,
          serverSide: true,
          ajax: 'server-side-php-script-url',
          "pagingType": "simple_incremental_bootstrap"
        });

Everything will work properly if I send 'recordsTotal' in the server response. But I don't want to count the total entries because of performance issues. So I tried to use the pagination plugin simple_incremental_bootstrap. However it is not working as expected. The next button always return the first page itself. If I give 'recordsTotal' in server response this plugin will work properly. I found out that If we don't give 'recordsTotal', the 'start' param sent by datatable to server side script is always 0. So my server side script will always return the first page.

According to this discussion, server side processing without calculating total count is not possible because “DataTables uses the record count that is passed back to it to deal with the paging controls”. The suggested workaround is “So the display records are needed, but it would be possible to just pass back a static number (like 1'000'000 or whatever) which would make DataTables think there are a million rows. You could hide the information element if this information is totally bogus!”

I wonder if anybody have a solution for this. Basically I want to have a simple pagination in my datatable with ajax without sending total count from server.

colin

Hi @Sibin ,

This here might work. I'm modifying the returned data from the server to cap the number of records. There should be "57" records, with that change, it's showing at most "30". You could get the server returning an arbitrary constant, with the client then changing the value to suit your needs.

Hope that helps,

Cheers,

Colin

Sibin

Hi @colin,
What you have said above is right, but it won't resolve my problem.

If I don't send recordsTotal from server, the pagination won't work properly. If I send a high static number as recordsTotal, table will show an active Next button even if there is no data in next page.

So I ended up in a solution which utilizes two parameters received in ajax script - 'start' and 'length'.

If rows in current page is less than 'limit' there is no data in next page. So total count will be 'start' + 'current page count'. This will disable Next button in the last page.

If rows in current page is equal to or greater than 'limit' there is more data in next pages. Then I will fetch data for next page. If there is at least one row in next page, send recordsTotal something larger than 'start'. This will display an active Next button.

Sample code:

$limit = require_param('length');
$offset = require_param('start');
$current_page_data = fn_to_calculate_data($limit, $offset);
$data = “get data $current_page_data”;
$current_page_count = mysqli_num_rows($current_page_data);
if($current_page_count >= $limit) {
  $next_page_data  = fn_to_calculate_data($limit, $offset+$limit);
  $next_page_count = mysqli_num_rows($next_page_data);
  if($next_page_count >= $limit) {
    // Not the exact count, just indicate that we have more pages to show.
    $total_count = $offset+(2*$limit);
  } else {
    $total_count = $offset+$limit+$next_page_count;
  }
} else {
  $total_count = $offset+$current_page_count;
}
$filtered_count = $total_count;

send_json(array(
  'draw' => $params['draw'],
  'recordsTotal' => $total_count,
  'recordsFiltered' => $filtered_count,
  'data' => $data)
);

However this solution adds some load to server as it additionally calculate count of rows in next page. Anyway it is a small load as compared to the calculation total rows.

We need to hide the count information from table footer and use simple pagination.

          dtOptions.pagingType = "simple";
          dtOptions.fnDrawCallback = function(oSettings) {
            $('#'+table_id+"_info").hide();
          };