Using ColdFusion CFCs to return data to DataTables 1.8.2

Posted by Jon on Dec 7, 2011 in ColdFusion, Datatables, jQuery | 7 comments

Just a quick post, more as a reminder for myself really, about a quick an easy way to return a query via ajax and easily load into a DataTable.

Simple CFC function:

<cffunction name="GetUsers" access="remote" output="false" returntype="any" returnformat="json">

  <cfquery name="local.qryUsers" datasource="dsn">
  SELECT id, name, surname, email FROM tblUsers
  </cfquery>

  <cfscript>
  local.strData = StructNew();
  local.strData['aaData'] = QueryToArray(local.qryCandidates);
  return local.strData;
  </cfscript>

</cffunction>

This example uses Ben Nadels QueryToArray function to convert our query object to a format that DataTables can now use (as of V1.8)

Then all you have to do is use the ‘aoColumns’ option in datatables to map your data to your columns, like so:

var userTable = $('#users).dataTable({
  "sAjaxSource": "path/to/handler.cfc?method=GetUsers",
  "aoColumns": [
    { "mDataProp": "id" , "sTitle": "ID"},
    { "mDataProp": "name" , "sTitle": "Name"},
    { "mDataProp": "surname" , "sTitle": "Surname"},
    { "mDataProp": "email" , "sTitle": "Email Address"}
  ]
});