Pagination not working on server side rendering

greennn

I have got server side rendering working using datatables but I cannot get pagination to work which means there is no advantage of using server side processing as I am rendering 3,000 rows at the same page right now. I am setting both recordsTotal and recordsFilter in my server side code but it is simply not working.

let tblAssignedJobs = $('#tblAssignedJobs').DataTable({
    "oLanguage": {
        "sEmptyTable": "There are no jobs assigned to you at this moment"
    },
    autoWidth  : false
   ,serverSide:true
    ,responsive : true
    ,deferRender    : true
    ,processing : true
    ,paging     : true
    ,pageLength : 25
    ,searching  : true
    ,info       : true
    ,ordering       : true
    ,dom            : "<ipf>"
    ,bPaginate  : false
    ,sDom       :"fptip",
    "aoColumns": [{
        "mData":"studentNumber"
      },{
        "mData": "studentType"
      }
      ,
      {
        "mData":"Description"
      }
      ,
      {
        "mData":"LocationNumber"
      }
      ,
      {
        "mData":"Address"
      },
      { "mData":"StudentNumber",
        "mRender": function(data, type, full) {

            return `<a href = '/editStudent?studentNumber=${data}'><button class='btn btn-primary'>Edit Job</button></a>`
        } 
      }
    ],
    ajax: {
        url: "assignedJobs",
        type: "POST"
    },

    responsive: true
});

On my Nodejs backend server:

let result = await rows.query(My Query)

myJSON = { 
    "recordsTotal": result.recordset.length,
    "recordsFiltered": 25,
    "sEcho":10,
    "data": result.recordset
    }
 res.send(myJSON)

kthorngren

How many rows of data is in this?

data": result.recordset

My guess is 3000 since you are using "recordsTotal": result.recordset.length, to set the records total. The amount of rows returned from your server script is expected to match the page size, pageLength : 25.

"recordsFiltered": 25,

I'm guessing this is incorrect. It should reflect the number of records to display after filtering.

"sEcho":10,

Is not a parameter that Server Side Processing is looking for. However it does require the draw parameter to be used which is a sequence number. The Server Side Processing docs detail the parameters used.

This example will give you a better idea of what should happen with the communications. Click on the Ajax tab and perform searches and sorting to see what is provided in the response.

Basically your server script is responsible to querying for the correct data and returning just one page of data. When using a SQL type DB you would use things like OFFSET and LIMIT for this.

Kevin

tangerine

You have this:

bPaginate  : false

You also have dom and sDom options, which contradict each other.
Names with Hungarian notation are legacy code, not generally used today.

greennn

I am totally confused. Even when I change the json to

   myJSON = { 
    "draw": 1,
    "recordsTotal": 25,
    "recordsFiltered": 25,
    "sEcho":25,
    "data": result.recordset
    }

I still get 3000 records. I know I can limit the result by doing top 25 in my select query but I will always get those 25 records only and nothing more. I would love to see some implementation of server side rendering with SQL database.

kthorngren

You can download and look at the server side script used by the SSP examples. The SSP examples are here. When looking at them there are tabs above the table to see the Ajax response and the script used.

I know I can limit the result by doing top 25 in my select query but I will always get those 25 records only and nothing more

Did you look at the OFFSET and LIMIT link I provided? This is how you pull 25 records from anywhere in the DB table.

Kevin

kthorngren

The other option is to not use server side processing and use deferRender. I see you have it in your config but its not helping. As Tangerine noted you have bPaginate : false which doesn't allow deferRender to do its job. Remove both serverSide:true and bPaginate : false from your config. Now you will show 25 rows, using pageLength : 25, and have paging. DeferRender will kick in and only render the elements need for the page being displayed. This may help speed things up for you.

Kevin

greennn

Kevin, I did have defer render before or at least I thought I did because it was pretty slow on mobile devices. The pages renders pretty fast when I load on laptop but when I use a cheap tablet like Samsung tab A it was taking 10-12 seconds to load 3000 rows. But I just did what you told me to and the page is only taking 4-5 seconds which is kind of acceptable. Upon my research I found that is never a good idea to let client do the computation as some client can have pretty weak processors. If I will always have less than a million rows do you think I should just stick to defer rendering for now?

I now understand the offset and limit and kind of get an idea of what I can do when it comes to server side rendering but I am still confused why page numbers are not appearing. Cause in my head if I can have page numbers rendering I can figure out a way to send a post request on page change like if users press page 4 then OFFSET would be 3*25, and limit would be 25.

I am having a hard time understanding the server side code that you linked. TBH I did not think it would be this difficult

colin

Upon my research I found that is never a good idea to let client do the computation as some client can have pretty weak processors. If I will always have less than a million rows do you think I should just stick to defer rendering for now?

For 3k records, you'd probably be better off using client-side processing. This section of the FAQ should help, it discusses various techniques to improve performance,

If that doesn't help, we're happy to take a look, but as per the forum rules, please link to a test case - a test case that replicates the issue will ensure you'll get a quick and accurate response. Information on how to create a test case (if you aren't able to link to the page you are working on) is available here.

Colin