JSON Pagination structure for datatables?

latinunit

I've never used pagination before, and I am finding it hard to grasp.

Given that my application has no concept of pagination, I am going to have to build one in JavaScript as that is the language used in my application to query data.

I am going through some documentation online to understand how to build it https://reqbin.com/req/yqyqa5ve/json-pagination-example but I don't know if this is something that datatables understands? does it follow/understand the links?

here is the structure of my JSON data

https://codebeautify.org/jsonviewer/y23424892 sample:

[{
    "id": "13297734",
    "cmOneID": "134891",
    "email": "13300339@email.com",
    "name": "F13300339 S13300339",
    "jurisdiction": "SCPB - LON",
    "blackList": "0",
    "rm": "Domenica Zampatti",
    "lawfulBasis": "Consent given",
    "subscriptions": "3218160,3218150,15656200,3218161"
  },
  {
    "id": "13218386",
    "cmOneID": "500131",
    "email": "500131@email.com",
    "name": "500131 - First Name 500131 - Last Name",
    "jurisdiction": "Guernsey",
    "blackList": "0",
    "rm": "Euan Dangerfield",
    "lawfulBasis": "None",
    "subscriptions": ""
  }]

Can someone provide me with how the paginated response structure should look like so that I can query 100k records and load them in batches of 1k to prevent my webapp from freezing, giving that if I load all 100k records its superslow.

rf1234

I would recommend to purchase an Editor license and turn on paging in data tables and serverSide, too. Done. Of course you can also do this without Editor. Then you have to make sure your server side script supports serverSide processing by only retrieving the records of the current page and the like. I never bothered to learn how to do this because Editor does this all for you - and the license is very cheap. It even makes sense to use Editor when you don't want to edit the records client side.

It is really that easy and it works fully automatically.

I am using it with a lot more than 100k records.

https://datatables.net/reference/option/paging
https://datatables.net/reference/option/serverSide

More details on pagination and customization of pagination:
https://datatables.net/examples/basic_init/alt_pagination.html

And on server side processing:
https://datatables.net/examples/server_side/

allan

I've no wish to talk you put of purchasing an Editor license, but you can actually use the server-side libraries without an Editor license.

If your JSON data structure can't be changes, then you will have to use client-side processing and load the full set up front, allowing DataTables to do the paging for you.

If you can change the data structure, then you could use server-side processing, per rf1234's links.

Allan

latinunit

Hi @allan, if is definately required, my client would purchase it i guess, but for now, I need to understand the limitations of the platform that I am working on, I am building an API to return the results that I require, but i need to build the pagination logic into that, which I have no prior kwowledge of experience. so I am learning as I go.

I have created this endpoint that can be used as json data source, it has the same/required object structure that I am currently feeding into datatables, and I created the logic so that I can pass params on the URL to limit the results. (&startLine=0&lineCount=3)

But according to this website https://reqbin.com/req/yqyqa5ve/json-pagination-example
it seems that I need to group or provide some links in mt json response? what is the required structure by datatables ? i am so confused.

...
"links": {
  "prev": "/echo/get/json?page=1&limit=50",
  "next": "/echo/get/json?page=3&limit=50"
}
...

allan

Are you using the reqbin API? If so, then you'll need a translation layer between DataTables' server-side processing parameters and Reqbin's, since they are not exactly the same.

If you aren't using their API, check out the documentation here which shows the parameters DataTables sends to the server and also what it expects back in return.

Two questions:

• What is your server-side environment? (e.g. PHP, .NET, Node.js, etc)
• What is your data source? (e.g. a MySQL database, Postgres, etc)

Allan

latinunit

I am not using reqbin, is just for reference to understand how pagination works, I am creating the endpoint from scratch in .jssp and I will post the code here for reference.

I use Adobe campaign, which uses XML, E4X, javascript (es5) rendering engine the underlying database is postgreSql (here is documented how I access data through the layers https://experienceleague.adobe.com/docs/campaign-classic/using/configuring-campaign-classic/api/data-oriented-apis.html?lang=en)

<%@ page import="/nl/core/shared/nl.js"%>
<%  

//required server-side dependencies
NL.ns('NL.API');
NL.require('/nl/core/shared/core.js')
  .require('/nl/core/jsspcontext.js');

//response headers
response.setContentType("application/json");
response.addHeader("Access-Control-Allow-Origin", "*")
response.addHeader("Pragma", "no-cache");
response.addHeader("Cache-Control", "no-cache");
response.addHeader("Date", new Date());    
response.addHeader("Expires", new Date());        

          //require authentication
          var jsspContext = new NL.JSSPContext(request);
                
            if( !jsspContext.checkAuthentication() ) {              
              response.sendError(403, "Authentication required");
              return;              
            } else if(request['method'] != 'GET'){ //API only accepts requests made through POST
               response.sendError(405, "Method Not Allowed ["+request['method']+"]");
              return; 
             }                         



var sl = request.getParameter('startLine'); 
var lc = request.getParameter('lineCount'); 

try {   
       
    

 var query = xtk.queryDef.create(<queryDef schema="nms:recipient" operation="select" startLine = {sl} lineCount={lc}> 
                                          <select>                                                                                      
                                             <node expr="[lnkRelationshipManager/@firstName]+ ' ' + [lnkRelationshipManager/@lastName]" alias="@rm"/>
                                             <node expr="Iif(@clawfulBasis = 0,'Removed',Iif(@clawfulBasis = 1,'None',Iif(@clawfulBasis = 2, 'Legitimate interest', 'Consent given')))" alias="@law"/>
                                             <node expr="@blackList"/>
                                             <node expr="@jurisdiction"/>
                                             <node expr="@firstName+' '+@lastName" alias="@name"/>
                                             <node expr="@email"/>   
                                             <node expr="@cmOneID"/>
                                             <node expr="@id"/>
                                             <node expr="[subscription/service/@id]"/> 
                                          </select>                
                                              <where>
                                              <condition>                                                                                                                                                                                         
                                              </condition>
                                            </where>                                                                          
                                         </queryDef> );
                                                                                 
  var data = query.ExecuteQuery();   
//  logInfo(data);
  
  var response = []; 
  for each(var i in data) { //iterate recipient data
    var rcpSubs = []; 
    for each (var s in i.subscription) { //iterate recipient subscriptions
      rcpSubs.push(s.service.@id);
    }   
  
    response.push({
                    "id"            : i.@id.toString(),
                    "cmOneID"       : i.@cmOneID.toString(),
                    "email"         : i.@email.toString(),
                    "name"          : i.@name.toString(),  
                    "jurisdiction"  : i.@jurisdiction.toString(),  
                    "blackList"     : i.@blackList.toString(),                     
                    "rm"            : i.@rm.toString(),
                    "lawfulBasis"   : i.@law.toString(),
                    "subscriptions" : rcpSubs.toString(),          
                   });   
                   
}
  
document.write(JSON.stringify(response));
console.log(response)                   

                  
} catch (err) {//catch errors
  response = {'error':err.toString()};
  document.write(JSON.stringify(response));
  console.log(JSON.stringify(response));
  } 
            
%>

rf1234

I've no wish to talk you put of purchasing an Editor license, but you can actually use the server-side libraries without an Editor license.

I wasn't aware of that! So there is no licensing requirement for what I suggested above.

It even makes sense to use Editor when you don't want to edit the records client side.

Yes, and it is even free of charge.

latinunit

@rf1234 Thanks for your advice, I am not allowed to use any other tool,library outside of what is already avaiable ootb on this application to query the database. so thats a big limitation.

allan

In terms of understanding pagination - if you break it down, it is actually quite simple. The client side says to the server "I want to display page 1, give me the records". Then "I want to display page 2, give me the records".

There is a little more nuance, such as "I want 10 records per page" and the server saying "Okay, in that case there are 100 pages of data", but that is all encapsulated in the client / server data communication used by DataTables.

If you aren't allowed to use our server-side processing libraries for Node.js, then you'll need to implement the JS code for the the parameters documented here.

Allan

latinunit

Hi @allan

Im learning as I go, so I have followed the serverside protocol and introduced the remaining variables, now my endpoint responds as follows

Complete response https://codebeautify.org/jsonviewer/y23fe7133

In my init config

/** datatables **/     
var table= $('#recipients').DataTable({   
        serverSide: true,
        processing:true,
        ajax: {
                url: '/sch/subscriptionAPI_v11.jssp?&startLine=0&lineCount=100',
                type: 'POST',
                dataSrc: "data"
               },         

Here is my endpoint new code https://www.codedump.xyz/ts/ZAoS4E6NLd6UF9uM

As you can see, my endpoint is defining the limiting factors which the endpoint code then uses to return specified counts, but this is wrong, because then is not dynamic, I need to understand, how to capture the request limiting factors, just like I am capturing the draw value through the request using the code

var draw = request.getParameter('draw');

allan

I need to understand, how to capture the request limiting factors, just like I am capturing the draw value through the request using the code

That's really something that you'd need to refer to the documentation for your server-side environment for. I guess you'd use request.getParameter('length'); and request.getParameter('start'); - but refer to their documentation for the nested properties such as search etc.

Note that recordsTotal and recordsFiltered should be identical in the result, unless there is a return specified.

Allan

latinunit

Ok this is going the correct direction, I was able to read from the request and remove from the url param the limiting factors, now the start and length is being correctly set to my query and is returning the correct amoun of records, but for some reason my datatables is broken,

For now, just wondering about recordsTotal and recordsFiltered as according to the following thread, recordsTotal is the amount of records in my database which is 50k and (i created a function to return the number of records in the table) and I am setting the records fitered to the amount of records in the data object which is set out my the limiting factor sent by datatables.

https://datatables.net/forums/discussion/69743/server-side-pagination-sql-server-and-datatables

allan

I am setting the records fitered to the amount of records in the data object which is set out my the limiting factor sent by datatables.

Yes - per the manual:

recordsFiltered: Total records, after filtering (i.e. the total number of records after filtering has been applied - not just the number of records being returned for this page of data).

i.e. it is not just the number of items returned for the page - DataTables can easily just do data.length to get that. It is the number of records across all pages after filtering.

Allan