mhenke/datatables.cfc

## datatables.cfc
<!--- place in /controllers folder--->
<cfcomponent extends="Controller">

	<cffunction name="getdemo" returntype="Query" access="private" >
		<cfset var entries = model("entry").findAll(select="BODY,CATEGORYID,TITLE,dateCreated") />
		<cfreturn entries />
	</cffunction>

	<cffunction name="init">
		<cfset filters(through="turnOffDebugging",only="processing")>
	</cffunction>

	<cffunction name="processing">
		<cfset var thisColumn = "" />
		<cfset var listColumns = "" />
		<cfset var listColumnsCast = "" />
		<cfset var objAttributes = structNew() />
		<cfset var queryObject = "" />
		<cfset var arrayMetaData = "" />

		<!--- query to use, which is in a function call of the same name but with get prepended --->
		<cfparam name="params.name" type="string" />
		<cfparam name="params.sIndexColumn" type="string" default="1" />
		<cfparam name="params.iDisplayStart" type="integer" default="0"  />
		<cfparam name="params.iDisplayLength" type="integer" default="10"  />
		<cfparam name="params.sEcho" type="string" default="10"  />
		<cfparam name="params.sSearch" type="string" default="" />
		<cfparam name="params.iSortingCols" type="integer" default="0"  />

		<cfset queryObject = evaluate("get#name#()") />
		<cfset arrayMetaData = getMetaData(queryObject) />

		<cfloop index="thisColumn"  from="1" to="#arraylen(arrayMetaData)#">
			<cfset listColumns = ListAppend(listColumns, arrayMetaData[thisColumn]["Name"]) />
			<cfset listColumnsCast = ListAppend(listColumnsCast, "CAST(#arrayMetaData[thisColumn]["Name"]#  as VARCHAR) as #arrayMetaData[thisColumn]["Name"]#") />
		</cfloop>

		<!--- set query of queries --->
		<cfset objAttributes.dbtype = "query"/>

		<!--- set columns to varchar --->
		<cfset objAttributes.name="queryObject" />
		<cfquery attributecollection="#objAttributes#">
			SELECT #listColumnsCast#
			FROM queryObject
		</cfquery>

		<!--- query name for filtering data set--->
		<cfset objAttributes.name="qFiltered" />

		<!--- Data set after filtering --->
		<cfquery attributecollection="#objAttributes#">
			SELECT #listColumns#
			FROM queryObject

			<!--- setup where clause --->
			<cfif len(trim(params.sSearch))>
				WHERE
				1 = 0
				<!--- filter --->
				<cfloop index="thisColumn"  from="1" to="#arraylen(arrayMetaData)#">
					OR upper(#arrayMetaData[thisColumn]["Name"]# ) LIKE <cfqueryparam value="%#ucase(trim(params.sSearch))#%" cfsqltype="cf_sql_varchar">
				</cfloop>
			</cfif>
		</cfquery>

		<!--- set length for the initial unfiltered data set --->
		<cfset qCount.total = queryObject.recordcount />

		<!--- Output --->
		<cfsavecontent variable="json_output">
		{"sEcho": <cfoutput>#val(params.sEcho)#</cfoutput>,
		"iTotalRecords": <cfoutput>#qCount.total#</cfoutput>,
		"iTotalDisplayRecords": <cfoutput>#qFiltered.recordCount#</cfoutput>,
		"aaData": [
			<cfoutput query="qFiltered" startrow="#val(params.iDisplayStart+1)#" maxrows="#val(params.iDisplayLength)#">
				<cfif currentRow gt (params.iDisplayStart+1)>,</cfif>
				[<cfloop list="#listColumns#" index="thisColumn">
					<cfif thisColumn neq listFirst(listColumns)>,</cfif>
					#serializeJSON(qFiltered[thisColumn][qFiltered.currentRow])#
				</cfloop>
				]
			</cfoutput>
			] }
		</cfsavecontent>

		<cfset renderText(json_output)>
	</cffunction>

	 <cffunction name="turnOffDebugging">
		 <!--- turn off debugging output --->
		<cfsetting showDebugOutput="No">
	 </cffunction>
</cfcomponent>

## demo.cfm
<!--- place in /views/datatables folder--->
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01//EN" "http://www.w3.org/TR/html4/strict.dtd">
<html>

	<head>
		<cfoutput>
		#javaScriptIncludeTag("jquery-1.4.2.min")#
		#javaScriptIncludeTag("jquery.dataTables.min")#
		#stylesheetLinkTag("demo_table")#
		</cfoutput>

		<cfset myAction = "demo" />
		<cfset myActionID = "#myAction#_table_id" />

		<!--- eventually break this into a call --->
		<script type="text/javascript" charset="utf-8">
			$(document).ready(function() {
				$('#<cfoutput>#myActionID#</cfoutput>').dataTable( {
					"bProcessing": true,
					"bServerSide": true,
					"sPaginationType": "full_numbers",
					"sAjaxSource": "/index.cfm/datatables/processing?name=<cfoutput>#myAction#</cfoutput>"
				} );
			} );
		</script>
	</head>

	<body id="dt_example">
		<div id="container">
			<div id="dynamic">
				<!--- eventually break this into a call --->
				<table cellpadding="0" cellspacing="0" border="0" class="display" id="<cfoutput>#myActionID#</cfoutput>">
					<thead>
						<tr>
							<th>Rendering engine</th>
							<th>Browser</th>
							<th>Platform(s)</th>
							<th>Engine version</th>
						</tr>
					</thead>
					<tbody>
						<tr>
							<td colspan="4" class="dataTables_empty">Loading data from server</td>
						</tr>
					</tbody>
					<tfoot>
						<tr>
							<th>Rendering engine</th>
							<th>Browser</th>
							<th>Platform(s)</th>
							<th>Engine version</th>
						</tr>
					</tfoot>
				</table>
			</div>
		</div>
	</body>
</html>
	<!--- place in /controllers folder--->
	<cfcomponent extends="Controller">

	<cffunction name="getdemo" returntype="Query" access="private" >
	<cfset var entries = model("entry").findAll(select="BODY,CATEGORYID,TITLE,dateCreated") />
	<cfreturn entries />
	</cffunction>

	<cffunction name="init">
	<cfset filters(through="turnOffDebugging",only="processing")>
	</cffunction>

	<cffunction name="processing">
	<cfset var thisColumn = "" />
	<cfset var listColumns = "" />
	<cfset var listColumnsCast = "" />
	<cfset var objAttributes = structNew() />
	<cfset var queryObject = "" />
	<cfset var arrayMetaData = "" />

	<!--- query to use, which is in a function call of the same name but with get prepended --->
	<cfparam name="params.name" type="string" />
	<cfparam name="params.sIndexColumn" type="string" default="1" />
	<cfparam name="params.iDisplayStart" type="integer" default="0" />
	<cfparam name="params.iDisplayLength" type="integer" default="10" />
	<cfparam name="params.sEcho" type="string" default="10" />
	<cfparam name="params.sSearch" type="string" default="" />
	<cfparam name="params.iSortingCols" type="integer" default="0" />

	<cfset queryObject = evaluate("get#name#()") />
	<cfset arrayMetaData = getMetaData(queryObject) />

	<cfloop index="thisColumn" from="1" to="#arraylen(arrayMetaData)#">
	<cfset listColumns = ListAppend(listColumns, arrayMetaData[thisColumn]["Name"]) />
	<cfset listColumnsCast = ListAppend(listColumnsCast, "CAST(#arrayMetaData[thisColumn]["Name"]# as VARCHAR) as #arrayMetaData[thisColumn]["Name"]#") />
	</cfloop>

	<!--- set query of queries --->
	<cfset objAttributes.dbtype = "query"/>

	<!--- set columns to varchar --->
	<cfset objAttributes.name="queryObject" />
	<cfquery attributecollection="#objAttributes#">
	SELECT #listColumnsCast#
	FROM queryObject
	</cfquery>

	<!--- query name for filtering data set--->
	<cfset objAttributes.name="qFiltered" />

	<!--- Data set after filtering --->
	<cfquery attributecollection="#objAttributes#">
	SELECT #listColumns#
	FROM queryObject

	<!--- setup where clause --->
	<cfif len(trim(params.sSearch))>
	WHERE
	1 = 0
	<!--- filter --->
	<cfloop index="thisColumn" from="1" to="#arraylen(arrayMetaData)#">
	OR upper(#arrayMetaData[thisColumn]["Name"]# ) LIKE <cfqueryparam value="%#ucase(trim(params.sSearch))#%" cfsqltype="cf_sql_varchar">
	</cfloop>
	</cfif>
	</cfquery>

	<!--- set length for the initial unfiltered data set --->
	<cfset qCount.total = queryObject.recordcount />

	<!--- Output --->
	<cfsavecontent variable="json_output">
	{"sEcho": <cfoutput>#val(params.sEcho)#</cfoutput>,
	"iTotalRecords": <cfoutput>#qCount.total#</cfoutput>,
	"iTotalDisplayRecords": <cfoutput>#qFiltered.recordCount#</cfoutput>,
	"aaData": [
	<cfoutput query="qFiltered" startrow="#val(params.iDisplayStart+1)#" maxrows="#val(params.iDisplayLength)#">
	<cfif currentRow gt (params.iDisplayStart+1)>,</cfif>
	[<cfloop list="#listColumns#" index="thisColumn">
	<cfif thisColumn neq listFirst(listColumns)>,</cfif>
	#serializeJSON(qFiltered[thisColumn][qFiltered.currentRow])#
	</cfloop>
	]
	</cfoutput>
	] }
	</cfsavecontent>

	<cfset renderText(json_output)>
	</cffunction>

	<cffunction name="turnOffDebugging">
	<!--- turn off debugging output --->
	<cfsetting showDebugOutput="No">
	</cffunction>
	</cfcomponent>
	<!--- place in /views/datatables folder--->
	<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01//EN" "http://www.w3.org/TR/html4/strict.dtd">
	<html>

	<head>
	<cfoutput>
	#javaScriptIncludeTag("jquery-1.4.2.min")#
	#javaScriptIncludeTag("jquery.dataTables.min")#
	#stylesheetLinkTag("demo_table")#
	</cfoutput>

	<cfset myAction = "demo" />
	<cfset myActionID = "#myAction#_table_id" />

	<!--- eventually break this into a call --->
	<script type="text/javascript" charset="utf-8">
	$(document).ready(function() {
	$('#<cfoutput>#myActionID#</cfoutput>').dataTable( {
	"bProcessing": true,
	"bServerSide": true,
	"sPaginationType": "full_numbers",
	"sAjaxSource": "/index.cfm/datatables/processing?name=<cfoutput>#myAction#</cfoutput>"
	} );
	} );
	</script>
	</head>

	<body id="dt_example">
	<div id="container">
	<div id="dynamic">
	<!--- eventually break this into a call --->
	<table cellpadding="0" cellspacing="0" border="0" class="display" id="<cfoutput>#myActionID#</cfoutput>">
	<thead>
	<tr>
	<th>Rendering engine</th>
	<th>Browser</th>
	<th>Platform(s)</th>
	<th>Engine version</th>
	</tr>
	</thead>
	<tbody>
	<tr>
	<td colspan="4" class="dataTables_empty">Loading data from server</td>
	</tr>
	</tbody>
	<tfoot>
	<tr>
	<th>Rendering engine</th>
	<th>Browser</th>
	<th>Platform(s)</th>
	<th>Engine version</th>
	</tr>
	</tfoot>
	</table>
	</div>
	</div>
	</body>
	</html>