CFWheels datatables example

index.cfm

<table id="datatable-users">
	<thead>
		<tr>
			<th>Id</th>
			<th>Created at</th>
			<th>First name</th>
			<th>Last name</th>
			<th>Email address</th>
		</tr>
	</thead>
</table>

<script type="text/javascript">
var dt = $('#datatable-users').DataTable({
	processing: true,
	serverSide: true,
	ajax: {
		url: '/users.json'
	},
	columns: [
		{name: 'id', data: 'id'},
		{name: 'createdAt', data: {'_' : 'createdAt.display', 'sort' : 'createdAt.sort'}, searchable: false},
		{name: 'firstName', data: 'firstName'},
		{name: 'lastName', data: 'lastName'},
		{name: 'email', data: 'email'}
	],
	order: [[0, 'asc']]
});
</script>

Users.cfc

<cfcomponent extends="Controller">

	
	<cffunction name="index">
		<cfif params.format eq "json">

			<!--- create the where clause for all searchable fields--->
			<cfset where = "">
			<cfif Len(params.search.value)>
				<cfset where = "id = #Val(params.search.value)#
					OR lastName LIKE '%#params.search.value#%' 
					OR firstName LIKE '%#params.search.value#%' 
					OR email LIKE '%#params.search.value#%'"
					>
			</cfif>
			
			<!--- create the order-by clause --->
			<cfset order = "">
			<cfif StructKeyExists(params, "order")>
				<cfloop collection="#params.order#" item="i">
					<cfset order = ListAppend(
						order, 
						"#params.columns[params.order[i].column].name# #UCase(params.order[i].dir)#"
						)>
				</cfloop>
			</cfif>

			<!--- calculate the current page --->
			<cfset page = (params.start + params.length) / params.length>

			<!--- get the data --->
			<cfset users = model("User").findAll(
				select = "id,createdAt,firstName,lastName,email", 
				where = where, 
				order = order, 
				page = page, 
				perPage = params.length
				)>

			<!--- format the data --->
			<cfset data = ArrayNew(1)>
			<cfloop query="users">
				<cfset row = StructNew()>
				<cfset row["id"] = linkTo(action="show", key=users.id, text=users.id)>
				<cfset row["createdAt"] = {
					"display" = DateFormat(users.createdAt, "dd mmm yy"), 
					"sort" = users.createdAt.getTime()
					}>
				<cfset row["firstName"] = users.firstName>
				<cfset row["lastName"] = users.lastName>
				<cfset row["email"] = users.email>
				<cfset ArrayAppend(data, row)>
			</cfloop>

			<!--- create a result struct and add record counts --->
			<cfset result["data"] = data>
			<cfset result["recordsFiltered"] = pagination().totalRecords>
			<cfset result["recordsTotal"] = model("User").count()>

			<!--- render the result as json --->
			<cfcontent type="application/json">
			<cfset renderText(SerializeJSON(result))>

		</cfif>
	</cffunction>


</cfcomponent>