Pagination on server side calls doesn't seem to be working correctly.

kthorngren

Post the following information:

1. Current Datatables init code
2. "columns" : columns, post the contents of the columns variable
3. Use the browser's network inspector to capture and post the full XHR Request parameters
4. Capture the XHR response from the same request collected in step 3

Kevin

athanasius

Init code:

var url = $this.attr('dataLoadURL')+ '&' + 'header=0&' + systemVars + '&s='+$.now();
            var columnUrl = $this.attr('dataLoadURL')+ '&' + 'header=1&' + systemVars + '&s='+$.now();
            var columns = [];
            $.ajax({
                url: columnUrl,
                method: 'post',
                success: function (data) {
                    data = JSON.parse(data);
                    columnNames = Object.keys(data.data[0]);
                    for (var i in columnNames) {
                      columns.push({data: columnNames[i], 
                                title: columnNames[i]});
                    }
                    $('#'+thisID).DataTable({
                        "columns" : columns,
                        "processing": true,
                        "retrieve": true,
                        "serverSide": true,
                        "search": {
                            return: true
                        },
                        "ajax": {
                            "url": url,
                            "contentType": "application/json",
                            "type": "POST",
                            "data": function ( d ) {
                              return JSON.stringify( d );
                            }
                        }
                    });
                }

Columns

0: {data: 'Tools', title: 'Tools', mData: 'Tools', sTitle: 'Tools'}
1: {data: 'Name', title: 'Name', mData: 'Name', sTitle: 'Name'}
2: {data: 'User ID', title: 'User ID', mData: 'User ID', sTitle: 'User ID'}
3: {data: 'Status', title: 'Status', mData: 'Status', sTitle: 'Status'}
4: {data: 'Email', title: 'Email', mData: 'Email', sTitle: 'Email'}
5: {data: 'Work Phone', title: 'Work Phone', mData: 'Work Phone', sTitle: 'Work Phone'}
6: {data: 'Modify By', title: 'Modify By', mData: 'Modify By', sTitle: 'Modify By'}
7: {data: 'Modify Date', title: 'Modify Date', mData: 'Modify Date', sTitle: 'Modify Date'}
8: {data: 'Created By', title: 'Created By', mData: 'Created By', sTitle: 'Created By'}
9: {data: 'Created Date', title: 'Created Date', mData: 'Created Date', sTitle: 'Created Date'}
length: 9

Request

https://url/&header=0&hasManager=false&isAdmin=true&hasHrRep=false&hasSupervisor=false&s=1646848138836

Response
```
{"draw": 1,
"recordsTotal": 9999,
"recordsFiltered": 8888,
"arguments": {"active":1,"datamismatch":0,"disabled":0,"draw":1,"header":"0","inactive":0,"length":10,"nolls":0,"order":null,"search":"","sc":"","start":0,"s":"1646848138836","hasHrRep":"false","hasManager":"false","isAdmin":"true","hasSupervisor":"false"},
"data": [

                {
                    "Tools": "<button type='button' title='Edit' class='editRecord' buttonURL='url'>Edit</button>",
                    "Name": "name",
                    "User ID": "userid",
                    "Status": "Disabled",
                    "Email": "email",
                    "Work Phone": "phone",
                    "Modify By": "",
                    "Modify Date": "N/A",
                    "Created By": "",
                    "Created Date": "date"
                }



                ,
                {
                    "Tools": "<button type='button' title='Edit' class='editRecord' buttonURL='url'>Edit</button>",
                    "Name": "name",
                    "User ID": "userid",
                    "Status": "Disabled",
                    "Email": "email",
                    "Work Phone": "phone",
                    "Modify By": "",
                    "Modify Date": "N/A",
                    "Created By": "",
                    "Created Date": "date"
                }



                ,
                {
                    "Tools": "<button type='button' title='Edit' class='editRecord' buttonURL='url'>Edit</button>",
                    "Name": "name",
                    "User ID": "userid",
                    "Status": "Disabled",
                    "Email": "email",
                    "Work Phone": "phone",
                    "Modify By": "",
                    "Modify Date": "N/A",
                    "Created By": "",
                    "Created Date": "date"
                }



                ,
                {
                    "Tools": "<button type='button' title='Edit' class='editRecord' buttonURL='url'>Edit</button>",
                    "Name": "name",
                    "User ID": "userid",
                    "Status": "Disabled",
                    "Email": "email",
                    "Work Phone": "phone",
                    "Modify By": "",
                    "Modify Date": "N/A",
                    "Created By": "",
                    "Created Date": "date"
                }



                ,
                {
                    "Tools": "<button type='button' title='Edit' class='editRecord' buttonURL='url'>Edit</button>",
                    "Name": "name",
                    "User ID": "userid",
                    "Status": "Disabled",
                    "Email": "email",
                    "Work Phone": "phone",
                    "Modify By": "",
                    "Modify Date": "N/A",
                    "Created By": "",
                    "Created Date": "date"
                }



                ,
                {
                    "Tools": "<button type='button' title='Edit' class='editRecord' buttonURL='url'>Edit</button>",
                    "Name": "name",
                    "User ID": "userid",
                    "Status": "Disabled",
                    "Email": "email",
                    "Work Phone": "phone",
                    "Modify By": "",
                    "Modify Date": "N/A",
                    "Created By": "",
                    "Created Date": "date"
                }



                ,
                {
                    "Tools": "<button type='button' title='Edit' class='editRecord' buttonURL='url'>Edit</button>",
                    "Name": "name",
                    "User ID": "userid",
                    "Status": "Disabled",
                    "Email": "email",
                    "Work Phone": "phone",
                    "Modify By": "",
                    "Modify Date": "N/A",
                    "Created By": "",
                    "Created Date": "date"
                }



                ,
                {
                    "Tools": "<button type='button' title='Edit' class='editRecord' buttonURL='url'>Edit</button>",
                    "Name": "name",
                    "User ID": "userid",
                    "Status": "Disabled",
                    "Email": "email",
                    "Work Phone": "phone",
                    "Modify By": "",
                    "Modify Date": "N/A",
                    "Created By": "",
                    "Created Date": "date"
                }



                ,
                {
                    "Tools": "<button type='button' title='Edit' class='editRecord' buttonURL='url'>Edit</button>",
                    "Name": "name",
                    "User ID": "userid",
                    "Status": "Disabled",
                    "Email": "email",
                    "Work Phone": "phone",
                    "Modify By": "",
                    "Modify Date": "N/A",
                    "Created By": "",
                    "Created Date": "date"
                }



                ,
                {
                    "Tools": "<button type='button' title='Edit' class='editRecord' buttonURL='url'>Edit</button>",
                    "Name": "name",
                    "User ID": "userid",
                    "Status": "Disabled",
                    "Email": "email",
                    "Work Phone": "phone",
                    "Modify By": "",
                    "Modify Date": "N/A",
                    "Created By": "",
                    "Created Date": "date"
                }


        ]}

kthorngren

You posted the request URL. I asked for the Request parameters, for example:

Everything looks ok. Did this not work? If not what happened?

Kevin

athanasius

When trying to sort or page, the processing bar covers the table but never goes away, even after the data is returned. I can see the returned json in the xhr window but it doesn't refresh the table.

Request parameters

{"draw":1,"columns":[{"data":"Tools","name":"","searchable":true,"orderable":true,"search":{"value":"","regex":false}},{"data":"Name","name":"","searchable":true,"orderable":true,"search":{"value":"","regex":false}},{"data":"User ID","name":"","searchable":true,"orderable":true,"search":{"value":"","regex":false}},{"data":"Status","name":"","searchable":true,"orderable":true,"search":{"value":"","regex":false}},{"data":"Email","name":"","searchable":true,"orderable":true,"search":{"value":"","regex":false}},{"data":"Work Phone","name":"","searchable":true,"orderable":true,"search":{"value":"","regex":false}},{"data":"Modify By","name":"","searchable":true,"orderable":true,"search":{"value":"","regex":false}},{"data":"Modify Date","name":"","searchable":true,"orderable":true,"search":{"value":"","regex":false}},{"data":"Created By","name":"","searchable":true,"orderable":true,"search":{"value":"","regex":false}},{"data":"Created Date","name":"","searchable":true,"orderable":true,"search":{"value":"","regex":false}}],"order":[{"column":0,"dir":"asc"}],"start":0,"length":10,"search":{"value":"","regex":false}}

kthorngren

You posted the response not the request

If that response you posted is after sorting or paging then the problem seems to be with the "draw":1. The draw parameter, as explained in the SSP docs is a sequence number. For each draw Datatables increments the parameter so I would expect the Request from the client will have a draw parameter value above 1 once you start paging, searching or storing. Your server script needs to respond with the draw value that was sent to it.

Kevin

athanasius

The data I posted above is from the Request Payload window. The server is setting the draw response to the same value as what it receives.

kthorngren

Sorry, you are right - I didn't look close enough. Please post a request payload and response to show the issue when you page or sort.

Kevin

athanasius

So with the revised code that I added from this thread I now only get the variables explicitly in the url sent to the server instead of the whole data table payload. Therefore, the draw variable will only be included if I make it part of the url which still leaves me with no data on the columns or sorting.

kthorngren

So with the revised code that I added from this thread I now only get the variables explicitly in the url sent to the server instead of the whole data table payload.

Assuming you still have serverSide: true and using the -ajax.data function to convert the parameters to a JSON string I created this example to show the request parameters are sent:
http://live.datatables.net/rosejayi/1/edit

There is no response because the server expects the default format of the parameters.

Can you post a link to your page or a running test case showing the issue so we can help debug?
https://datatables.net/manual/tech-notes/10#How-to-provide-a-test-case

Kevin

athanasius

I figured out that having the method set to "post" removes the data tables payload. I'll work on this some more and let you know.

kthorngren

Using POST removes the Datatabe server side parameters from the URL but the are still sent but its sent as formData. Your server script will need to process the POST formData instead of the URL parameters.

Kevin

athanasius

So if I do a post request, I lose all of the data tables payload. If I change it to a get request, I get a 400 error for unexpected characters.

athanasius

Okay. I'll try that.

athanasius

The gethttprequestdata() function in ColdFusion accesses those form variables passed by the datatable when using a post request. I almost have it all working now.

athanasius

So I have sorting and searching working. I'm down to my last major issue. Each row needs a custom button for editing the row. The edit url information is encrypted on the backend before being added to the data being sent back to datatables.

The problem is that the created button, since it is built through datatables, doesn't have the onClick event attached to it that it had when we were doing this client side. How can I attach the event once the data is passed back to datatables?

Server side build for each row button:

"data": [
            
                <cfoutput query="result" startrow="#val(start+1)#" maxrows="#val(arguments.length)#">
                    <cfset buttonURL = urlEncodedFormat(encryptfunction(key,"id=#result.id#&s=#Now()#"))>

                    <cfif result.currentRow gt (start+1)>,</cfif>
                    {
                        "Tools": "<button type='button' title='Edit' class='editRecord' buttonURL='#buttonURL#'>Edit</button>",
                        "Name": #SerializeJSON(result.name)#,
                        "User ID": #SerializeJSON(result.user_id)#
                    }
                </cfoutput>
]}

Existing datatables jquery code:

var url = $this.attr('dataLoadURL')+ '&' + 'header=0&' + systemVars + '&s='+$.now();
            var columnUrl = $this.attr('dataLoadURL')+ '&' + 'header=1&' + systemVars + '&s='+$.now();
            var columns = [];
            console.log(url);
            $.ajax({
                url: columnUrl,
                success: function (data) {
                    console.log(data);
                    data = JSON.parse(data);
                    columnNames = Object.keys(data.data[0]);
                    for (var i in columnNames) {
                      columns.push({data: columnNames[i], 
                                title: columnNames[i]});
                    } 
                    $('#'+thisID).DataTable({
                        "columns" : columns,
                        "processing": true,
                        "retrieve": true,
                        "serverSide": true,
                        "search": {
                            return: true
                        },
                        
                        "ajax": {
                            "url": url,
                            "contentType": "application/json",
                            "type": "POST",
                            "dataType": "json",
                            "data": function ( d ) {
                              return JSON.stringify( d );
                            }
                        }
                    });
                },
            error: function (e) {
                console.log(e);
            }
            });

The existing onClick function that no longer is working:

$('.editRecord').on('click', function(e){
    e.preventDefault();
    editRecordURL = 'editrecord.cfm?' + $(this).attr('buttonURL');
    window.location = editRecordURL;
});

```

kthorngren

Use delegated events as shown in this example.

Kevin

iansr

With some editing that does work except if there are two buttons in the same tr. Then they both get the same buttonURL added to the end. Using .each doesn't fix it.

$('#'+thisID + ' tbody').on('click', 'tr', function (e) {
                        e.preventDefault();
                        var row = this;
                        $(row).find("button").each(function() {
                            var button = this;
                            editRecordURL = '/editUser.cfm?' + $(button).attr('buttonURL');
                            window.location = editRecordURL;
                         });
                         
                    });

iansr

I also tried this, pulling the url builder outside but it doesn't seem to grab the buttons with the binder class.

$('#'+thisID+' tr').find('.binder').each(function() { 
                        $this = $(this);
                        thisFunc = $this.attr('thisFunc');
                        $this.on('click', function() {
                                    window[thisFunc](this);
                        });
                    });

External function:

function buildUrl(t) {
    thisURL = $(t).attr('thisURL');
    console.log(thisURL);
    editRecordURL = '/editUser.cfm?' + $(button).attr('buttonURL');
    window.location = editRecordURL;
}

Buttons:

<button type='button' title='Edit' class='binder' thisFunc='buildUrl' thisURL='#buttonURL#'>Edit</button> 
<button type='button' title='Edit2' class='binder' thisFunc='buildUrl' thisURL='#buttonURL2#'>Edit</button>

kthorngren

Use something like this example:
http://live.datatables.net/xijecupo/1/edit

Use a classname or input name attribute to distinguish between the buttons with your click event.

Kevin

iansr

Is it possible to do that with an unknown number of buttons?

The problem I'm running into is that they want this to be a single piece of code that will work on different reports that may have one or more buttons, possibly more than two.

kthorngren

Its just standard Javascript and jQuery methods you need to use. There is nothing specific to Datatables. You will need to find a way to define the buttons so you know which one is clicked. Take a look on Stack Overflow for ideas that might work for your situation. For example this thread might ive you some ideas.

Kevin

iansr

The trick was to wrap the function in the following. Thank you for all the help getting me to here.

table.on('draw', function(){
  $('#'+thisID+' tr').find('.binder').each(function() {
                        $this = $(this);
                        thisFunc = $this.attr('thisFunc');
                        $this.on('click', function() {
                                    window[thisFunc](this);
                        });
                    });
}