Skip to content

Instantly share code, notes, and snippets.

@mhenke
Created September 21, 2010 15:07
Show Gist options
  • Save mhenke/589813 to your computer and use it in GitHub Desktop.
Save mhenke/589813 to your computer and use it in GitHub Desktop.
<!--- on initial request, run query --->
<cfset "session.#attributes.myactionfile#" = "" />
<cfsavecontent variable="datasorter_js">
<style type="text/css" title="currentStyle">
@import "css/datatables_table.css";
</style>
<script type="text/javascript" language="javascript" src="js/jquery.dataTables.min.js"></script>
<script type="text/javascript" charset="utf-8">
$(document).ready(function() {
$('#<cfoutput>#attributes.myactionfile#</cfoutput>').dataTable( {
"bProcessing": true,
"bServerSide": true,
"sPaginationType": "full_numbers",
"sAjaxSource": "<cfoutput>#attributes.myactionfile#</cfoutput>.cfm"
} );
} );
</script>
</cfsavecontent>
<cfhtmlhead text = "#datasorter_js#">
<!--- sets filename as session variable --->
<cfset filename = evaluate("replace(GetFileFromPath(GetCurrentTemplatePath()),'.cfm','')")>
<cfparam name="session.#evaluate("filename")#" default="">
<!--- if sessions not set on server or initial request, the variable will not be a query --->
<cfif not IsQuery(evaluate("session.#filename#"))>
<!--- initial data set for query of queries --->
<cfquery name="qrydatatable" datasource="#application.DSN#">
<!---
query goes here
NO ORDER BY NEEDED HERE WILL BE DONE BY QoQ USING #sIndexColumn#
--->
</cfquery>
<!--- SQL queries Get data to display --->
<cfset "session.#filename#" = qrydatatable>
</cfif>
<!--- initial sorting column --->
<cfset sIndexColumn = "funding_opportunity_num" />
<!--- generic processing (no need to touch)--->
<cfinclude template="xxx_datatables_processing.cfm" />
<!---
Script: DataTables server-side script for ColdFusion (cfm) and MySQL
License: GPL v2 or BSD (3-point)
Notes:
tested with DataTables 1.6.1 and jQuery 1.2.6+, Adobe ColdFusion 9 (but should work fine on at least 7+)
to work with pre 1.6 datatables replace both occurances of sSortDir_ with iSortDir_
Get a free developer version of ColdFusion from http://www.adobe.com/products/coldfusion/
or try out the open source railo cfml engine from http://www.getrailo.org/
or try out the open source openbd cfml engine from http://www.openbluedragon.org/
--->
<!--- turn off debugging output --->
<cfsetting showDebugOutput="No">
<cfset listColumns = "" />
<cfset listColumnsCast = "" />
<cfset flag_cast = 0 />
<cfset arrayMetaData = getMetaData(evaluate("session.#filename#")) />
<cfset qOriginal = replace(de('session.#filename#'),'"','','all') />
<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"]#") />
<cfif arrayMetaData[thisColumn]["TypeName"] NEQ "VARCHAR">
<cfset flag_cast = 1 />
</cfif>
</cfloop>
<!--- set query of queries --->
<cfset objAttributes.dbtype = "query"/>
<cfif flag_cast>
<!--- set columns to varchar --->
<cfset objAttributes.name="queryObject" />
<cfquery attributecollection="#objAttributes#">
SELECT #listColumnsCast#
FROM #qOriginal#
</cfquery>
</cfif>
<!--- query name for filtering data set--->
<cfset objAttributes.name="qFiltered" />
<!---
Paging
--->
<cfparam name="url.iDisplayStart" default="0" type="integer" />
<cfparam name="url.iDisplayLength" default="10" type="integer" />
<cfparam name="url.sIndexColumn" default="1" />
<cfparam name="url.sSearch" default="" type="string" />
<!---
Ordering
--->
<cfparam name="url.iSortingCols" default="0" type="integer" />
<!--- Data set after filtering --->
<cfquery attributecollection="#objAttributes#">
SELECT #listColumns#
FROM #qOriginal#
<!--- setup where clause --->
<cfif len(trim(url.sSearch))>
WHERE
1 = 0
<!--- filter --->
<cfloop index="thisColumn" from="1" to="#arraylen(arrayMetaData)#">
OR upper(#arrayMetaData[thisColumn]["Name"]# )
LIKE <cfqueryparam value="%#ucase(trim(url.sSearch))#%" cfsqltype="CF_SQL_#arrayMetaData[thisColumn]["TypeName"]#">
</cfloop>
</cfif>
<!--- Ordering --->
ORDER BY
<cfif url.iSortingCols gt 0>
<cfloop from="0" to="#url.iSortingCols-1#" index="thisS">
<cfif thisS is not 0>, </cfif>
#listGetAt(listColumns,(url["iSortCol_"&thisS]+1))#
<cfif listFindNoCase("asc,desc",url["sSortDir_"&thisS]) gt 0>#url["sSortDir_"&thisS]#</cfif>
</cfloop>
<cfelse>
#sIndexColumn#
</cfif>
</cfquery>
<!--- set length for the initial unfiltered data set --->
<cfset qCount.total = evaluate("session.#filename#.recordcount")>
<!--- Output --->
<cfcontent reset="Yes" />
{"sEcho": <cfoutput>#val(url.sEcho)#</cfoutput>,
"iTotalRecords": <cfoutput>#qCount.total#</cfoutput>,
"iTotalDisplayRecords": <cfoutput>#qFiltered.recordCount#</cfoutput>,
"aaData": [
<cfoutput query="qFiltered" startrow="#val(url.iDisplayStart+1)#" maxrows="#val(url.iDisplayLength)#">
<cfif currentRow gt (url.iDisplayStart+1)>,</cfif>
[<cfloop list="#listColumns#" index="thisColumn">
<cfif thisColumn neq listFirst(listColumns)>,</cfif>
#serializeJSON(qFiltered[thisColumn][qFiltered.currentRow])#
</cfloop>
]
</cfoutput>
] }
<!--- APPLICATION.GetApplicationSettings().SessionManagement --->
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01//EN" "http://www.w3.org/TR/html4/strict.dtd">
<html>
<cf_datatables2 myactionfile="xxx_datatables_action">
<body id="dt_example">
<div id="container">
<div id="dynamic">
<table cellpadding="0" cellspacing="0" border="0" class="display" id="xxx_datatables_action">
<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>
</body>
</html>
@mhenke
Copy link
Author

mhenke commented Oct 2, 2010

need to use cast in query of having user in query set datatype.

    <cfset listColumnsCast = ListAppend(listColumnsCast, "CAST(#arrayMetaData[thisColumn]["Name"]# as VARCHAR) as #arrayMetaData[thisColumn]["Name"]#") />
    <!--- set columns to varchar --->
    <cfset objAttributes.name="queryObject" />
    <cfquery attributecollection="#objAttributes#">
        SELECT #listColumnsCast# 
        FROM queryObject
    </cfquery>

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment