Ajax server side operations, info section not displaying correct values on page load

landslideadventurer

I've been customising Datatables V2 to use Ajax with server side filtering.

I'm trying to get the client side completed first and have been able to get it to a stage where selecting pages or sorting columns is set on the query parameters so refreshing the page loads the corresponding data.

I've been able to pass through the page parameter to the server and retrieve the data for the specific page and set the pager to select the correct page, but I can't seem to figure out how to set the info section as it always starts from the first page.

For example, my page size is 25 and on page 5.

Selecting Page 5 normally would show showing 100 to 125 of 150, but when the screen is refreshed and data is loaded initially, the info will show up as showing 1 to 25 of 150.

I can get this to work if i do a redraw, but that means i have the initial call to the server followed by another call to the server for the same data just to update the info section - which I do not want.

My code is as follows (omitted some parts as needed).

this.dtTableElement.DataTable({
  processing: true,
  serverSide: true,
  ajax: {
    url: `/api/Data/Grid`,
    type: 'GET',
    data: function (d) {
      return {
        draw: d.draw,
        start: helperFunctions.isFirstLoad ? (initParams.page ? (parseInt(initParams.page) - 1) * d.length : 0) : d.start, // get the start value number - correctly set e.g. 100
        length: d.length, // length - set at 25
        search: helperFunctions.isFirstLoad ? initParams.search : d.search.value,
        order: helperFunctions.isFirstLoad ? initParams.order : (d.order.length > 0 ? `${d.columns[d.order[0].column].data},${d.order[0].dir}` : ''),
        filterColumns: d.columns
          .filter(col => col.searchable)
          .map(col => col.data)
          .join(','),
        page: helperFunctions.isFirstLoad ? initParams.page : Math.floor(d.start / d.length) + 1 // send what page this would be - just to get the return data to say what page we're at
      };
    },
    dataFilter: function (data) { // looking at dataFilter i can see that my data is coming back as expected
      var json = JSON.parse(data);
      return JSON.stringify({
        draw: json.Draw,
        recordsTotal: json.Total || 0,
        recordsFiltered: json.Filtered || 0,
        data: json.Items || []
      });
    }
  },
  layout: {
    topStart: null,
    topEnd: null,
    bottomStart: 'info',
    bottomEnd: 'paging'
  },
  initComplete: function (settings, json) {
    if (helperFunctions.isFirstLoad && initParams.page) {
      const startPage = parseInt(initParams.page) - 1;
      helperFunctions.dataTable.page(startPage); // updates the pager, but doesnt update the info. doing a draw here updates the pager and info at the same time, but makes another call to the server which I dont want.
    }

    helperFunctions.isFirstLoad = false;
  },
  drawCallback: function (settings) {
    helperFunctions.updateUrlParams(); // just updates the query params on the url so it can be used to load data.
  }
});

I'm not even sure if I'm doing this correctly so if not, would be great if anyone can provide guidance for this.

Thanks

allan

You need to change displayStart if you want to change where DataTables starts its page load from.

The problem at the moment (as I understand it) is that DataTables is saying "can I have page 1 please", but your Ajax code is overruling that and saying to the server-side "Let's have page 5 please", which of course DataTables doesn't know about.

Your other parameters will have a similar issue. DataTables expects back what is has asked for, not something else.

You might consider looking into the built in stateSave option, as it seems like the sort of thing you are looking to do?

Allan

landslideadventurer

Thanks for the update Allan!

The displayStart has fixed it!

So setting the following works:

displayStart: helperFunctions.isFirstLoad ? (initParams.page ? (parseInt(initParams.page) - 1) * 25 : 0) : 0,

And with this, I can remove the manual page assignment on the initComplete callback as well.

I've had a look at the page you've mentioned with stateSave and it looks very promising but it looks like it's its for a different functionality such as saving the configuration of the table how the user wants it, like preferences.

Is there any way to extract just the filtering / paging / sorting capability on the URL as a query param and for the table to read it back as a query param when the page loads so state isn't saved? This way a manual query can be created which will load the data for you exactly as described (unless it doesn't exist, then can default to page 1 as it does now).

allan

Is there any way to extract just the filtering / paging / sorting capability on the URL as a query param and for the table to read it back as a query param when the page loads so state isn't saved?

Yes, have a look at this blog post.

Allan

landslideadventurer

Hi Allan,

Thanks for the link to deeplinking, it looks like that was exactly what I was looking for.

I've updated my table to use deeplinking and have coupled this with a POST to the server to get server side operations working.

I initially wanted to use a GET but it was having issues with model binding so used POST instead.

Just want to check, are there any npm CDNs for the deeplinking.js file (instead of the dt official CDN)?

landslideadventurer

Also an extra question, how do you handle cases where if DisplayStart doesn't match the row count, then to just start at the beginning?

Like this on the examples page:

https://datatables.net/blog/2017/deep-linking?displayStart=500

Trying to start from 500 will fail as expected and just display from 1 onwards.

I've been able to update my server logic so if the display start is greater than the row count, then to start from 0, but I can't get my display to update accordingly - it says Showing 501 to 157 of 157 entries with the pager not selected (as there are no pages).

const deepLinkOptions = $.fn.dataTable.ext.deepLink(['search.search', 'order', 'displayStart']);

this.dtTableElement.DataTable($.extend(true, {
...
            columns: this.columnConfig,
            pageLength: 25, // Fixed page length
            lengthChange: false, // Disable length changing
            order: [[this.getColumnIndex(this.defaultSort.column), this.defaultSort.dir]],
            displayStart: 0, // Start at the beginning by default
...
}, deepLinkOptions );

allan

Just want to check, are there any npm CDNs for the deeplinking.js file (instead of the dt official CDN)?

Not yet - that is something that I need to do some work on. The resent plugins I've made I've published with their own npm package. I need to get around to doing that with all the old ones as well.

Also an extra question, how do you handle cases where if DisplayStart doesn't match the row count, then to just start at the beginning?

With client-side processing it is already handled as you've seen. With server-side processing, that is actually a limitation of the client / server protocol that DataTables currently implements. There isn't a mechanism for the server to say "nope - don't have those records, here are some different ones".

The only way to do that at the moment would be to include a flag in your JSON reply - resetRequired: true or something like that. Then add an event listener (xhr) to check for that flag, and if present call draw() to reset the paging. It would trigger a second Ajax request with a reset of the paging.

Expanding the client / server data interchange to allow for this is something that is on my todo list. Apologies that it isn't done yet.

Allan

landslideadventurer

Great, thanks for your help Allan!