Server side data tables page length is not working

wajid213

Hi,

I am facing issues of page Length is server side data table. it returns 36 recordsTotal and makes pagination page numbers perfect but it displays all records of 36 on the first page of pagination.

I have two pages having data tables, one is working fine and other is having this issue of pagelength but code is same for both.

i have tried to fix it but could not solve it.

,paging: true
,pageLength : 10

Thanks in advance if you could help me.

kthorngren

That not much info to go on. If the Datatables styling and functions aren't working then you likely have an error in your browser's console. To help we would need a link to your page or a test case replicating the issue.
https://datatables.net/manual/tech-notes/10#How-to-provide-a-test-case

Kevin

wajid213

Please have a look at this URL http://new.qrun.com.au/running-groups/
i have used Pagelength=10 and total records are 36 which are coming from the database but it displays all records in the first page instead of 10 records.

Thanks

kthorngren

Thanks for the link. Interesting page. Took a bit to find but I think the problem is with conflicting config options. You can the following:

        datatable=selectId.DataTable( {
            destroy: true,
            processing: true,
            serverSide: true,
            fixedColumns    : true,
            stateSave: false
            
            ,autoWidth  : false
            ,responsive : true
            ,deferRender    : true
            ,processing : true
            ,paging     : true
            ,pageLength : parseInt(vv)
            ,searching  : false
            ,info       : false
            ,ordering       : false
            ,dom            : "<ipf>"
            ,bPaginate  : false
            ,sDom       :"fptip"

bPaginate is the legacy version of paging. you have paging: true followed by bPaginate: false. I suspect the problem is the second overrides the first. Remove the bPaginate: false and see if it helps. Please see this regarding legacy to current parameter conversion:
https://datatables.net/upgrade/1.10-convert

Same for sDom and dom.
Kevin

wajid213

@kthorngren Thanks for your help. I have tried your solutions but could not succeed, I have used dom and sDom one by one separately and I also tried by removing bPaginate option but it gives the same result.

I have tried it by changing the sequence of config option also.
I have used this same sequence on another page it is working fine but this page still giving me the same result.

Please check it again and let me know if you could help me, I shall be very thankful to you.

Thanks

kthorngren

Looks like you are loading jQuery twice:

Line 72:
<script src="/qrun/js/external/jquery.min.js"></script>

and at the end of the page just before datatalbes.js:
<script src="/miscellaneous/javascripts/jquery-3.1.1.min.js"></script>

You will want to load this only once.

Kevin

wajid213

I have tried it by using jquery file one by one but could not succeed, it is giving the same result.

kthorngren

I'm wondering if the problem is that you have serverSide: true, but are returning 36 rows of data. With server side processing Datatables expects only the number of rows requested which corresponds to the page length.

In the server side processing doc for the returned data it has this for the data parameter.

The data to be displayed in the table. This is an array of data source objects, one for each row, which will be used by DataTables.

My recommendation is to only return the number of rows requested.

Kevin

wajid213

but why it is working fine on this page http://new.qrun.com.au/runners-handicap1/athlete-list/ ?

both pages have the same piece of code.

kthorngren

It works because your server is returning 60 array elements which your dataFilter function is parsing down to 30 rows of data out of over 4000 total records. Your problem page is returning 72 array elements which you are parsing into 36 rows of data.

Kevin

wajid213

One is returning 60 and it parses into 30 and it works fine but other is returning 70 which parses into 35.

how can I fix it?

kthorngren

The fix is in your server side script. Is it using the same server script script as the working page?

Kevin

wajid213

yes both pages are using same script

kthorngren

Looking at the ajax request they are sending different parameters.

Working page:

            data: function ( d ) {
                d.first_name = $('#s_first').val();
                d.handicap = $('#s_handicap').val();
                d.gender = $('#s_gender').val();
                d.distance = $('#s_distance').val();
                d.club = $('#s_club').val();
                d.flag = $('#flag').val();
                d.paginate = $('#paginate').val();
            },

Non working page:

            data: function ( d ) {
                d.first = $('#first').val();
                d.group_type = $('#s_group_type').val();
                d.region = $('#region').val();
                d.flag = $('#flag').val();
                d.club = $('#club').val();

            },

It appears the missing item for the non working page is d.paginate = $('#paginate').val();.

From the working page console:

$('#paginate').val();
"30"

From the non working page console:

$('#paginate').val();
undefined

The non working page doesn't have this element.

Kevin

wajid213

$('#paginate').val(); they are using for other purposes, it is not related to paginate value or for to get number of records..

it works same with these and without this also.

wajid213

d.club = $('#club').val(); d.paginate = $('#paginate').val();
i was working on to dynamically assign pageLength for both pages. One is using "paginate" and other is using "club" value to assign pageLength but now i am using static values instead of dynamic value for pageLenght but it returns same results.

kthorngren

The non working page is sending this server side processing request to your server:

method: listRunningClubs
draw: 1
columns[0][data]: 0
columns[0][name]: 
columns[0][searchable]: true
columns[0][orderable]: false
columns[0][search][value]: 
columns[0][search][regex]: false
start: 0
length: 20
search[value]: 
search[regex]: false
first: 
group_type: 
region: 
flag: 2
club: 10

The length value is 20 which matches what you have configured ,pageLength : 20 . This is the response:

 {sEcho: 1, iTotalRecords: 35, iTotalDisplayRecords: 35,…}
aaData: [[,…], ["", "", "", "", ""], [,…], ["", "", "", "", ""], [,…], ["", "", "", "", ""], [,…],…]
0: [,…]
1: ["", "", "", "", ""]
2: [,…]
3: ["", "", "", "", ""]
4: [,…]
5: ["", "", "", "", ""]
6: [,…]
7: ["", "", "", "", ""]
8: [,…]
9: ["", "", "", "", ""]
.......
62: [,…]
63: ["", "", "", "", ""]
64: [,…]
65: ["", "", "", "", ""]
66: [,…]
67: ["", "", "", "", ""]
68: [,…]
69: ["", "", "", "", ""]
iTotalDisplayRecords: 35
iTotalRecords: 35
sEcho: 1

The response has data for 35 rows not 20.

Your working page sends this request:

method: GetAthletes
draw: 1
columns[0][data]: 0
columns[0][name]: 
columns[0][searchable]: true
columns[0][orderable]: false
columns[0][search][value]: 
columns[0][search][regex]: false
start: 0
length: 30
search[value]: 
search[regex]: false
first_name: 
handicap: 
gender: 
distance: 
club: 
flag: 2
paginate: 30

The page length is 30 and the response has data for 30 rows of data.

 {sEcho: 1, iTotalRecords: 41054, iTotalDisplayRecords: 41054,…}
aaData: [[,…], ["", "", "", "", "", "", "", "", "", ""], [,…], ["", "", "", "", "", "", "", "", "", ""], [,…],…]
0: [,…]
1: ["", "", "", "", "", "", "", "", "", ""]
2: [,…]
3: ["", "", "", "", "", "", "", "", "", ""]
4: [,…]
5: ["", "", "", "", "", "", "", "", "", ""]
6: [,…]
........
56: [,…]
57: ["", "", "", "", "", "", "", "", "", ""]
58: [,…]
59: ["", "", "", "", "", "", "", "", "", ""]
iTotalDisplayRecords: 41054
iTotalRecords: 41054
sEcho: 1

I suspect the method parameter indicates to your server script which code to run to fetch the data. Is this true? If so then make sure the listRunningClubs is using the 'length' parameter to limit the amount of data fetched. Or make sure its doing the same as GetAthletes.

Again its an issue with your server script sending the incorrect amount of data.

Kevin

wajid213

Thanks for your explanation.

When I change 30 to 10 in getAthletes page then it returns data according to pegLength but when I change any number in listRunningClubs then it does not fetch results from the database according to pageLength which is statically defined...sequence of code is same in both pages

kthorngren

Not sure why the listRunningClubs response has more rows then the page length. I would look at the code to make sure its using the length parameter to limit the data and not another parameter. Datatables is sending the correct information. The problem is with the response.

Kevin

wajid213

As you can see both pages are using paging=true pageLength=static but it returns different responses in both datatables

wajid213

OK thanks dear for your help. i have found the issue in response page.