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

athanasius

I have built the backend processing for this in Coldfusion. When I run the call with endrow or maxrows set on the data build, the total rows and filtered rows seems to be ignored as the page only displays the default number of rows and doesn't allow paging, even though the total rows on the returned json shows several thousand records.

If I remove the maxrows and endrow attributes from the loop, the pagination looks correct but it also looks like the entire data set is being loaded at once instead of really doing server side processing.

jquery:

$.getJSON(url, function(json) {
                $('#'+thisID).DataTable({
                    data : json.data,
                    columns : json.columns
                });
            });

ColdFusion code within a cfc function:

<cfquery datasource="datasource" name="result">
        select
             *
        from db
    </cfquery>

    <!--- Total data set length --->
    <cfquery datasource="datasource" name="qCount">
        SELECT COUNT(id) as total
        FROM   db
    </cfquery>

    <cfif result.recordcount gt 0>
        <cfset recordsTotal = qCount.total>
    <cfelse>
        <cfset recordsTotal = 0>
    </cfif>
    
    <cfsavecontent variable="JsonObject">
        {"draw": <cfoutput>#val(arguments.draw)#</cfoutput>,
        "recordsTotal": <cfoutput>#recordsTotal#</cfoutput>,
        "recordsFiltered": <cfoutput>#result.recordCount#</cfoutput>,
        "columns": [
            
            {
                "title" : "ID",
                "data" : "id"
            },
            {
                "title" : "Name",
                "data" : "name"
            },
            {
                "title" : "User ID",
                "data" : "user_id"
            }
        ],
        "data": [
        <cfif result.recordcount gt 0>
            <cfoutput query="result" startrow="#val(arguments.start+1)#" endrow="#val(arguments.length)#">
                <cfif result.currentRow gt (arguments.start+1)>,</cfif>
                {
                    "id": #SerializeJSON(result.currentRow)#,
                    "name": #SerializeJSON(result.name)#,
                    "user_id": #SerializeJSON(result.user_id)#
                }
            </cfoutput>
        <cfelse>
            ""
        </cfif>
        ]}
    </cfsavecontent>

kthorngren

For server side processing to work you need to use the Datatables ajax option. Like these examples. The data option simply reads the data supplied. It has no concept of server side processing. Additionally for server side processing to work you need the serverSide option enabled and a server script that follows the server side processing protocol to handle the paging, sorting and searching operations.

Kevin

athanasius

Is it still possible to build the columns server side or do I have to have all the columns in the html table?

kthorngren

The columns.title will build the thead. You will need to have the columns defined before initializing Datatables, like you have in your first code snippet.

This is a client side with ajax loaded data example:
http://live.datatables.net/regatofe/1/edit

This is the same example using server side processing:
http://live.datatables.net/qimukefe/1/edit

Kevin

athanasius

Thank you.

athanasius

Is this the format I should be using for the returned json?

I get an error:

Cannot read properties of undefined (reading '0')

On this line:

columnNames = Object.keys(data.data[0]);

column header json

{"columns": [
                {
                    "title" : "ID"
                },
                {
                    "title" : "Name"
                },
                {
                    "title" : "User ID"
                }
            ]}

kthorngren

Cannot read properties of undefined (reading '0')

Maybe Object.keys(data.data[0]) is not appropriate for the JSON data being returned. The example I provided derives the column names from the first object in the array. You certainly don't have to do the same. You will need to extract the column information to build the columns configuration options that Datatables supports.

Kevin

athanasius

Is it possible to include the desired column names, instead of just using the db field values, in the data information? I thought I had seen that somewhere in the documentation but I cannot find it now.

kthorngren

Sure. Your server script can respond with the column information. Since its an ajax request outside of Datatables you can do what you want. The JS Bin environment is limited on ajax resource, so the example just uses the data it can fetch

Kevin

athanasius

Thank you for the help. It is working.

athanasius

Okay, the page is loading and paging correctly but the sorting variables don't seem to be getting sent back to the server when a column header is clicked.

Here's my revised calling 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,
                success: function (data) {
                    data = JSON.parse(data);
                    console.log(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,
                        "serverSide": true,
                        "search": {
                            return: true
                        },
                        "ajax": url
                    });
                }
            });

This is the data getting sent back to the server during the ajax call. It doesn't change no matter which column I click on the front end.

{"active":1,
"ci":null,
"columns":"",
"datamismatch":0,
"disabled":0,
"draw":"1",
"header":"0",
"inactive":0,
"length":"10",
"nolls":0,
"order":null,
"search":"",
"sc":"",
"start":"0",
"columns[2][search][regex]":"false",
"columns[0][orderable]":"true",
"columns[2][search][value]":"",
"search[regex]":"false",
"search[value]":"",
"columns[2][name]":"",
"columns[1][data]":"Name",
"columns[0][data]":"id",
"order[0][column]":"0",
"columns[1][name]":"",
"columns[2][orderable]":"true",
"columns[1][orderable]":"true",
"columns[1][searchable]":"true",
"columns[0][name]":"",
"columns[0][searchable]":"true",
"columns[2][searchable]":"true",
"columns[0][search][regex]":"false",
"columns[0][search][value]":"",
"order[0][dir]":"asc",
"columns[1][search][regex]":"false",
"columns[2][data]":"User ID",
"columns[1][search][value]":""}

kthorngren

The order is indicated with this:

"order[0][column]":"0",
"order[0][dir]":"asc",

See the Server Side Processing docs for details. The server script needs to look for these parameters to build the query to include column ordering.

Kevin

athanasius

Right, but the data that is sent to the server when a column header is clicked never changes from null to something that the server can process.

kthorngren

column header is clicked never changes from null to something that the server can process.

Not sure what null value is being sent to the server. Looking at this example. When the table first initializes it sends the default ordering of column 0 ASC and the draw parameter is 1:

draw: 1
order[0][column]: 0
order[0][dir]: asc

I Sort another column, the draw parameter increments and it sends the new column sorting:

draw: 2
order[0][column]: 2
order[0][dir]: asc

Is this not happening for you?

Kevin

kthorngren

I see some parameters like "datamismatch":0, which aren't sent by Datatables. Do you know where they are coming from?

I noticed the "order":null,. AFAIK this is not something Datatables sends, even if there is no ordering to the table. I suspect these parameters are coming from one of these statements:

var url = $this.attr('dataLoadURL')+ '&' + 'header=0&' + systemVars + '&s='+$.now();
            var columnUrl = $this.attr('dataLoadURL')+ '&' + 'header=1&' + systemVars + '&s='+$.now();

Maybe verify what systemVars are.

Kevin

athanasius

I'll check. Thanks.

athanasius

System vars is a cache clearing value for IE.

The data that is getting sent by dataTables to the server is strings of key-value pairs. I thought it was supposed to be structured in arrays. Is this correct? (Not all data included here.)

active  1   number  1

datamismatch    2   number  0

disabled    3   number  0

draw    4   string  1

header  5   string  0

inactive    6   number  0

length  7   string  10

nolls   8   number  0

sc  9   string  

start   10  string  0

columns[2][search][regex]   11  string  false

columns[0][orderable]   12  string  true

columns[2][search][value]   13  string  

search[regex]   14  string  false

columns[4][name]    15  string  

search[value]   16  string  

columns[9][orderable]   17  string  true

columns[2][name]    18  string  

kthorngren

The Server Side Processing doc describes the parameters sent and the expected parameters in the response. The parameters your are showing have more than what Datatables sends like datamismatch. Other than the extra parameters it looks correct. You can use the browser's network inspector tool with any of these Server Side Processing examples to see what is sent and the format.

Kevin

athanasius

But the data does not seem to be coming to the server as arrays like the docs say. Each variable is a separate string. On the server side I have tested the order and columns variables to see if they are arrays and they are being listed as individual strings for each row.

From the docs:

The order[i] and columns[i] parameters that are sent to the server are arrays of information:

order[i] - is an array defining how many columns are being ordered upon - i.e. if the array length is 1, then a single column sort is being performed, otherwise a multi-column sort is being performed.
columns[i] - an array defining all columns in the table.
In both cases, i is an integer which will change to indicate the array value. In most modern server-side scripting environments this data will automatically be available to you as an array.

athanasius

If I try to check if order is an array (isArray(arguments.order)), I get an error that "order" is not a key in the variables. I have to run a check on the entire order[][column] to be told that it is not an array.

kthorngren

jQuery ajax() will serialize the parameters. What you are seeing is how the arrays are serialized. Sounds like you are wanting to receive JSON data instead. See the last example in the ajax.data for how to send JSON data instead of the default you are seeing.

Kevin

athanasius

...

tangerine

Look at the ajax.data example again. You're not using it correctly.

athanasius

Right. I edited my comment when I noticed the problem but not quickly enough.

error: cannot read properties of undefined (reading 'aDataSort')

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({
                        "ajax": {
                            "url": url,
                            "columns" : columns,
                            "processing": true,
                            "serverSide": true,
                            "search": {
                                return: true
                            },
                            "contentType": "application/json",
                            "type": "POST",
                            "data": function ( d ) {
                              return JSON.stringify( d );
                            },
                            "retrieve": true
                        }
                    });
                }
            });

kthorngren

Lines 17-22 and 28 are inside your ajax option. They should be at the same level as your ajax option. for example:

                    $('#'+thisID).DataTable({
                        "columns" : columns,
                        "processing": true,
                        "ajax": {
                            "url": url,
                    ....

Kevin

athanasius

Fixed that and don't get errors but the table hangs on processing after the initial load when I try to page or sort. I did check the data being sent back to the jquery and it is the same as before which was working for paging.

                    $('#'+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 );
                            }
                        }
                    });

kthorngren

Sounds like the draw parameter doesn't match the value sent from the client.

Kevin

athanasius

Draw is set to 1 in the data being sent back from the server. I also confirmed that the data being sent back is properly formatted json.

athanasius

As an added oddity, the data being sent by jquery to the server no longer includes all of the column and order data like it did before.

All of this and the other similar values are missing now from what the server gets.

"columns[12][search][regex]":"false",
"columns[0][data]":"id",
"columns[7][search][value]":"",
"columns[5][searchable]":"true",
"order[0][column]":"0",
"columns[4][search][regex]":"false",
"columns[9][search][value]":"",
"columns[8][search][value]":"",

colin

Can you link to your page so we can take a look, it would help understand the data being returned,

Colin

athanasius

I wish I could but the system is on an intranet. Is there anything else I can do to provide more information? I would run this in the test environment but it's a ColdFusion application, not PHP.