load static json data with recordstotal coming from server

ari1322

My datatable code looks like this

$('#userTable1').DataTable({

    aLengthMenu: [[50, 100, 200], [50, 100, 200]],
    iDisplayLength: 50,
    "bProcessing": true,
    "bAutoWidth": true,
    serverSide: true,

    responsive: true,


    "data": dataSrcJ.data,


    aoColumns: [
      { mData: 'TOTAL_COUNT' },
      { mData: 'USER_NAME' },
      { mData: 'FULL_NAME' },
      { mData: 'EMAIL_ID' },
      { mData: 'IS_ADMIN' },
      {
        defaultContent: '<a data-target="#editUserModal" id="selectCurrent" class="edit" title="Edit" data-toggle="tooltip"><i class="fa fa-pencil-square"></i></a>',
        orderable: false
      },
      {
        mData: 'USER_ID',
        visible: false,
        searchable: false
      }
    ],


    "sDom": 'frtl<"bottom"><"clear"><"br">p',
    "bJQueryUI": true,
    destroy: true
  });

_________________________My Response looks like this------------------

{"recordsFiltered":99929,"data":[{"IS_ADMIN":"Admin","IS_LOCKED":"open","TOTAL_COUNT":1,"FULL_NAME":"iAmSayandrwrw5757","USER_ID":1,"EMAIL_ID":"arindam.mandal@tcgdigital.com","USER_NAME":"admin","CONTACT_NUMBER":"2147483647"},{"IS_ADMIN":"Admin","IS_LOCKED":"open","TOTAL_COUNT":2,"FULL_NAME":"iAmSayan","USER_ID":82,"EMAIL_ID":"abc@gmail.com","USER_NAME":"admin1","CONTACT_NUMBER":"2147483647"},{"IS_ADMIN":"Admin","IS_LOCKED":"open","TOTAL_COUNT":3,"FULL_NAME":"iAmSayan","USER_ID":83,"EMAIL_ID":"abc@gmail.com","USER_NAME":"admin2","CONTACT_NUMBER":"2147483647"},{"IS_ADMIN":"Admin","IS_LOCKED":"open","TOTAL_COUNT":4,"FULL_NAME":"iAmSayan","USER_ID":84,"EMAIL_ID":"abc@gmail.com","USER_NAME":"admin3","CONTACT_NUMBER":"2147483647"},{"IS_ADMIN":"Admin","IS_LOCKED":"open","TOTAL_COUNT":5,"FULL_NAME":"iAmSayan","USER_ID":85,"EMAIL_ID":"abc@gmail.com","USER_NAME":"admin4","CONTACT_NUMBER":"2147483647"},{"IS_ADMIN":"Admin","IS_LOCKED":"open","TOTAL_COUNT":6,"FULL_NAME":"iAmSayan","USER_ID":86,"EMAIL_ID":"abc@gmail.com","USER_NAME":"admin5","CONTACT_NUMBER":"2147483647"},{"IS_ADMIN":"Admin","IS_LOCKED":"open","TOTAL_COUNT":7,"FULL_NAME":"iAmSayan","USER_ID":87,"EMAIL_ID":"abc@gmail.com","USER_NAME":"admin6","CONTACT_NUMBER":"2147483647"},{"IS_ADMIN":"Admin","IS_LOCKED":"open","TOTAL_COUNT":8,"FULL_NAME":"iam admin_10","USER_ID":95,"EMAIL_ID":"abc@gmail.com","USER_NAME":"admin_10","CONTACT_NUMBER":"423424244"}],"recordsTotal":99929}

----------------------I want to give datatable recordsTotal information so that, although it is populating 50 data per page, but the pagination should show for all records.

kthorngren

Datatables supports using recordsTotal and recordsFiltered when Server Side Processing is enabled as part of the protocol. Datatables doesn't use these when using client side processing as it expects all the table rows to be at the client.

If you don't want to enable server side processing then you can create your own paging controls with a custom event handler that fetches the data and updates the table using clear() followed by rows.add().

Kevin

ari1322

Hi Kevin, is there any workaround to add recordsTotal in a static json?

===========
My problem is i want pagination option for total data in my db.

===Let me explain my whole problem.==========

---

$('#userTable1').dataTable({

    aLengthMenu: [[50, 100, 200], [50, 100, 200]],
    iDisplayLength: 50,
    "bProcessing": true,
    "bAutoWidth": true,
    serverSide: true,
    //stateSave: true,
    responsive: true,
    //info: true,
    //pagingType: 'full_numbers',
    sAjaxSource: 'getAllUsers/',
    aoColumns: [
      { mData: 'TOTAL_COUNT' },
      { mData: 'USER_NAME' },
      { mData: 'FULL_NAME' },
      { mData: 'EMAIL_ID' },
      { mData: 'IS_ADMIN' },
      {
        defaultContent: '<a data-target="#editUserModal" id="selectCurrent" class="edit" title="Edit" data-toggle="tooltip"><i class="fa fa-pencil-square"></i></a>',
        orderable: false
      },
      {
        mData: 'USER_ID',
        visible: false,
        searchable: false
      }
    ],
    fnServerData: function (sSource, aoData, fnCallback, oSettings) {
      oSettings.jqXHR = $.ajax({
        "contentType": 'application/json',
        "type": "POST",
        "url": sSource,
        data: JSON.stringify({
          "data": $.base64.encode(
            JSON.stringify({
              "CLIENT_ID": 104,
              "IS_ACTIVE": 1,
              "paginationInfo": 0,
              "perPageDataShow": 50

            })
          )
        }),

        success: fnCallback,
      });

    },
    "sDom": 'frtl<"bottom"><"clear"><"br">p',
    "bjQueryUI": true,
    destroy: true
  });

  var table = $('#userTable1').DataTable();




  table.on('page.dt', function () {

    paginationInfo = table.page();
    perPageDataShow = table.page.len();
    alert(paginationInfo);
    table.destroy();
    table.clear().draw(return_first(paginationInfo, perPageDataShow));

  });
  function return_first(paginationInfo, perPageDataShow) {
    var tmp = null;
    dataSrc = $.ajax({
      "contentType": 'application/json',
      "type": "POST",
      "url": "getAllUsers/",
      data: JSON.stringify({
        "data": $.base64.encode(
          JSON.stringify({
            "CLIENT_ID": 104,
            "IS_ACTIVE": 1,
            "paginationInfo": paginationInfo,
            "perPageDataShow": perPageDataShow

          })
        )
      }),
      'success': function (response) {
        //console.log(response)
        tmp = response;
        console.log(tmp)
        //populate_itemDetails_datatable(tmp, paginationInfo, perPageDataShow);


        $('#userTable1').DataTable({
          aLengthMenu: [[50, 100, 200], [50, 100, 200]],
          iDisplayLength: 50,
          "bProcessing": true,
          "bAutoWidth": true,
          serverSide: true,
          "bPaginate": true,
          responsive: true,
          //stateSave: true,
          sAjaxSource: 'getTotalUsersCount/',
          //data:tmp.data,

          fnServerData: function (sSource, aoData, fnCallback, oSettings) {
            oSettings.jqXHR = $.ajax({
              "contentType": 'application/json',
              "type": "POST",
              "url": sSource,
              data: JSON.stringify({
                "data": $.base64.encode(
                  JSON.stringify({
                    "CLIENT_ID": 104,
                    "IS_ACTIVE": 1
                  })
                )
              }),

              "success": fnCallback 
            });

          },
          aoColumns: [
            { mData: 'TOTAL_COUNT' },
            { mData: 'USER_NAME' },
            { mData: 'FULL_NAME' },
            { mData: 'EMAIL_ID' },
            { mData: 'IS_ADMIN' },
            {
              defaultContent: '<a data-target="#editUserModal" id="selectCurrent" class="edit" title="Edit" data-toggle="tooltip"><i class="fa fa-pencil-square"></i></a>',
              orderable: false
            },
            {
              mData: 'USER_ID',
              visible: false,
              searchable: false
            }
          ],


          "sDom": 'frtl<"bottom"><"clear"><"br">p',
          "bJQueryUI": true,
          destroy: true
        });


      }

    });

---

There is a default datatable loading on document.ready function. which is okay. Now if i want to click on page no. 5 it will get data per page and page no. accordingly forming my db query with upper and lower limit.
The issue is once i call the data table after clearing the first instance of the table, it is loading but without pagination, that's why i want to add it manually

kthorngren

Your code is confusing to me. Lets start by getting some basic information first. Most of your code is using Datatables 1.9 syntax but you also have some Datatables 1.10 API calls. I will assume you have Datatables 1.10.

I gather the real problem you are trying to solve is that the server code doesn't use the parameters sent by Datatables and you need to send these:

          "CLIENT_ID": 104,
          "IS_ACTIVE": 1,
          "paginationInfo": paginationInfo,
          "perPageDataShow": perPageDataShow

If you are using Datatables 1.10 it is recommended to use ajax instead of sAjaxSource and fnServerData. You can combine the two with the ajax option. Use ajax.data as a function to send the paginationInfo and perPageDataShow parameters, like this example. You can use the data parameter to get the paging info to set the paginationInfo and perPageDataShow values.

It looks like the response has the required recordsTotal, recordsFiltered and data parameters. But it is missing the draw parameter as described here.

Fixing this should allow you to remove 53-147 from your code.

The issue is once i call the data table after clearing the first instance of the table, it is loading but without pagination

Likely you are getting a Javascript error. Check the browsers console for errors.

Kevin