Ah, yeah I suppose I could have provided an example
Datatables requires a struct of data (the metadata, as it were) within which is an array of structs (the actual table data to render) is placed, so you’ll need to do a couple of things.
First, you’ll need to convert your query to an array of structs - The format of JSON returned by serializing a query directly is not one datatables will be able to cope with (and it just sucks as JSON anyway IMHO). EDIT: I stand corrected then if datatables can handle the format returned by CFML when serializing a query (honestly, I didn’t think it could lol). You can find a function to do this on Ben Nadel’s blog (Ask Ben: Converting a Query to an Array). Note that this code maintains UPPERCASE column names so you’ll have to account for that in your JavaScript. I, personally, just loop through the query and build my own array of structs, something like:
var returnStruct = {};
var returnArray = [];
var ix = 0;
for( var row in query ) {
ix++;
var rowStruct = {};
rowStruct[ 'column1' ] = row.column1;
rowStruct[ 'column2' ] = row.column2;
etc.
returnArray[ ix ] = rowStruct;
}
Then you add your array of structs to the returnStruct with the additional fields datatables requires in the JSON:
returnStruct[ 'draw' ] = form.draw; // comes from datatables when making the request and must be returned
returnStruct[ 'recordsTotal' ] = totalRecords // this is the total records available (before filtering with search, etc.)
returnStruct[ 'recordsFiltered' ] = query.recordCount; // total filtered records
returnStruct[ 'data' ] = returnArray; // this is the actual table data as an array of structs
Then you can serializeJson( returnStruct )
to return the data to datatables.
In the view (where datatables will live) you need to tell datatables you want to use processing and serverside options, along with the columns it should expect in the order it should expect them. For example:
$('#my-table').DataTable( {
'processing': true,
'serverSide': true,
'ajax': {
'url': '[URL to CFC to get the JSON data]',
'type': 'POST'
},
'columns': [
{ 'data': 'column1' },
{ 'data': 'column2' }
]
} );
That’s the basics - you can get more elaborate with it beyond that, of course. Dump out the FORM scope when datatables makes a request to see how it formats the data being sent in (sorting, search, etc.) so you can take appropriate action (sorting the query using ORDER BY based on the column(s) the user chooses to sort on, for example).
For example:
FORM[ 'search[value]' ]
is how the above code will present any value being searched for
FORM[ 'order[0][dir]' ]
is the order (asc or desc) for the first column being sorted in the table
FORM[ 'order[0][column]' ]
is the first column number (zero based) in the table being sorted to sort on
HTH
– Denny
P.S. Since you’re using Lucee, you can also eliminate the array indexing by using the append()
member function, for example:
for( var row in query ) {
var rowStruct = {};
rowStruct[ 'column1' ] = row.column1;
rowStruct[ 'column2' ] = row.column2;
etc.
returnArray.append( rowStruct );
}