I didn't forget about this set of posts, promise! Taking a break from the PHP stuff, I'm back with part 2 – replacing an Ajax-driven CFGRID display with a dataTables one. You might be asking, well why would I even want to use an Ajax-driven table versus just doing a regular HTML table and letting dataTables work it's magic? When you're dealing with smaller record sets, I would stick with the HTML table unless you really needed some sort of dynamic/live data feed, but if you are start dealing with 1000+ records, the HTML page becomes unwieldy and slow. Then it makes sense to switch to using Ajax for faster response and better performance.
Unlike with the previous example, the query itself is going to be a little different for the two versions, so I'll include that code in the respective sections. Also, in both cases, we'll have at least two files. So first, the CFGRID way.
<cfform>
<cfgrid
name="BookList"
align="Top"
autoWidth="yes"
bgColor="FFF"
colHeaderBold="yes"
format="html"
gridDataAlign="left"
gridLines="yes"
bind="cfc:test.getBooksForTable({cfgridpage},{cfgridpagesize},{cfgridsortcolumn},{cfgridsortdirection})"
sort="yes"
stripeRowColor="FC6"
stripeRows="yes">
<cfgridcolumn name="bookid" display="no" />
<cfgridcolumn name="title" header="Title" href="bookDetails.cfm?BookID=" hrefKey="bookid" target="_blank" width="300" />
<cfgridcolumn name="dateacquired" header="Acquired" dataalign="center" type="date" width="100" />
<cfgridcolumn name="genre" href="browse.cfm?GenreID=" hrefKey="genres_genreid" header="Genre" width="200" />
<cfgridcolumn name="authors" header="Author(s)" width="200" />
<cfgridcolumn name="formatlabel" href="browse.cfm?FormatID=" hrefKey="formats_formatid" header="Format" width="120" />
<cfgridcolumn name="seriesname" href="seriesDetails.cfm?BookID=" hrefKey="series_seriesid" header="Series Name" width="200" />
</cfgrid>
</cfform>
Now our code in the CFC that the GRID calls via Ajax.
<cfcomponent> <cfset variables.dsn = "sdg_training" /> <cffunction name="getBooksForTable" access="remote" returntype="struct" output="false"> <cfargument name="Page" type="string" required="false" default="" /> <cfargument name="PageSize" type="string" required="false" default="" /> <cfargument name="GridSortColumn" type="string" required="false" default="dateacquired" /> <cfargument name="GridSortDirection" type="string" required="false" default="DESC" /> <cfset var startRow = (Arguments.Page-1) * Arguments.PageSize /> <cfset var endRow = Arguments.Page * Arguments.PageSize /> <cfset var qBooks = "" /> <cfset var qAuthors = "" /> <cfset var myAuthors = "" /> <cfif NOT LEN(TRIM(Arguments.GridSortColumn))> <cfset Arguments.GridSortColumn = "dateacquired" /> <cfset Arguments.GridSortDirection = "DESC" /> </cfif> <!--- obviously in a real-world use case, we'd have some validations here to be sure the sort and page stuff were valid entries; and a try/catch in case of query failure ---> <cfquery name="qBooks" datasource="#variables.dsn#"> SELECT bookid, title, isbn, dateacquired, formats_formatid, genres_genreid, series_seriesid, series_position, amazon_asin, formatlabel, genre, seriesname, NULL AS authors FROM books </cfquery> <cfloop query="qBooks"> <cfquery name="qAuthors" datasource="#variables.dsn#"> SELECT authorid, CONCAT(firstname, " ", lastname) AS authorname FROM mylibrary_authors INNER JOIN mylibrary_books_to_authors ON mylibrary_authors.authorid = mylibrary_books_to_authors.authors_authorid WHERE books_bookid = <cfqueryparam value="#bookid#" cfsqltype="cf_sql_integer" /> ORDER BY authorid </cfquery> <cfset myAuthors = "" /> <cfloop query="qAuthors"> <cfset myAuthors = ListAppend(myAuthors, '<a href="authorDetails.cfm?AuthorID=#authorid#">#qAuthors.authorname#</a>') /> </cfloop> <cfset myAuthors = Replace(myAuthors, ",", ", ", "ALL") /> <cfset QuerySetCell(qBooks, "authors", myAuthors, currentRow) /> </cfloop> <cfquery name="qBooks" dbtype="query"> SELECT bookid, title, isbn, dateacquired, formats_formatid, genres_genreid, series_seriesid, series_position, amazon_asin, formatlabel, genre, seriesname, authors FROM qBooks ORDER BY #Arguments.GridSortColumn# #Arguments.GridSortDirection# </cfquery> <cfreturn QueryConvertForGrid(qBooks, Arguments.Page, Arguments.PageSize) /> </cffunction> </cfcomponent>
Notice that with this example, we have to return the results using the QueryConvertForGrid function, which does all the pagination stuff, rather than you doing it in the query itself. While this is convenient, it does mean that for every call, it always pulls all the results first before filtering – defeating at least part of the performance boost of using the Ajax call. We could increase the performance by caching, but that sort of defeats the idea of it being "live" data LOL. Also, you cannot display the number of records (vs number of pages) without writing more custom JavaScript functions, and the links that it generates do not actually work the way the documentation claims they will. *shaking head*
Anyway, here's what that one looks like:
And now, let's redo this using dataTables. Don't forget, the HEAD of the display has all the CSS/JS includes for dataTables, as shown in the previous example.
<h1>My Library</h1>
<table id="bookList">
<thead>
<th>Title</th>
<th>Acquired</th>
<th>Genre</th>
<th>Author(s)</th>
<th>Format</th>
<th>Series Name</th>
</thead>
<tbody></tbody>
</table>
<script type="text/javascript">
$.ajaxSetup({
dataFilter:function(data,type) {
if (data.substring(0,2) == "//") {
//remove "//", if there is one
data = data.substring(2,data.length)
}
return data
}
});
$(document).ajaxStop($.unblockUI);
$(document).ajaxError(function(event, jqXHR, ajaxSettings, thrownError) {
alert("There was an error loading this data. If this continues, please notify FirstCall.");
//alert(jqXHR);
//alert(thrownError);
//alert(event);
});
$(document).ready(function(){
$('#bookList').dataTable({
"bJQueryUI": true,
"bAutoWidth": true,
"sPaginationType": "full_numbers",
"aaSorting": [[ 1, "desc" ]],
"bProcessing": true,
"bServerSide": true,
"sAjaxSource": "test2.cfc?method=getBooksForTable",
"aoColumnDefs": [
{ "sWidth": "100px", "sClass": "alignCenter", "aTargets": [ 1 ] },
{ "sWidth": "100px", "aTargets": [ 4 ] }
],
"oLanguage": {
"sProcessing": "<img src='/assets/images/icons/coffeemachine.gif' align='middle' /> Loading library, please wait..."
}
});
});
</script>
And then it's CFC:
<cfcomponent>
<cfsetting showdebugoutput="false" />
<cfset variables.dsn = "sdg_training" />
<cffunction name="getBooksForTable" access="remote" returnformat="JSON" returntype="any" output="false">
<cfargument name="sEcho" type="numeric" required="true" default="1" />
<cfargument name="iDisplayStart" type="numeric" required="false" default="0" />
<cfargument name="iDisplayLength" type="numeric" required="false" default="10" />
<cfargument name="sSearch" type="string" required="false" default="" />
<cfset var sortField = "" />
<cfset var sortFieldValue = "" />
<cfset var sortFieldDirValue = "" />
<cfset var sortFieldDir = "" />
<cfset var orderFields = "" />
<cfset var FilterOrderBy = "" />
<cfset var aColumnArray = ListToArray("title,dateacquired,genre,authors,formatlabel,seriesname") />
<cfset var aGridData = ArrayNew(1) />
<cfset var aaData = arrayNew(1) />
<cfset var qAllBooks = "" />
<cfset var qBooks = "" />
<cfset var qFilteredBooks = "" />
<cfset var myAuthors = "" />
<!--- get our order by --->
<cfif IsDefined("Arguments.iSortCol_0")>
<cfloop from="0" to="#Arguments.iSortingCols#" index="x">
<cfset sortField = 'iSortCol_#x#' />
<cfset sortFieldDir = 'sSortDir_#x#' />
<cfif IsDefined("Arguments.#sortField#") EQ "YES">
<cfset sortFieldValue = Arguments[#sortField#] + 1 />
<cfset sortFieldDirValue = Arguments[#sortFieldDir#] />
<cfset orderFields = orderFields & aColumnArray[sortFieldValue] & " " & sortFieldDirValue & ", "/>
</cfif>
</cfloop>
<cfset FilterOrderBy = LEFT(orderFields, LEN(orderFields)-2) />
</cfif>
<!--- again in a real-world use case, we'd have some validations here to be sure the sort and page stuff were valid entries; and a try/catch in case of query failure --->
<!--- notice here we add a where statement to handle the filtering that dataTables has by default :-) --->
<cfquery name="qAllBooks" datasource="#variables.dsn#">
SELECT COUNT(DISTINCT books.bookid) AS allRecordCount
FROM books INNER JOIN mylibrary_books_to_authors ON books.bookid = mylibrary_books_to_authors.books_bookid
INNER JOIN mylibrary_authors ON mylibrary_authors.authorid = mylibrary_books_to_authors.authors_authorid
</cfquery>
<cfset var startRow = 1 />
<cfset var endRow = qAllBooks.RecordCount />
<cfif Arguments.iDisplayLength NEQ -1>
<cfset startRow = Arguments.iDisplayStart + 1 />
<cfset endRow = startRow + Arguments.iDisplayLength />
</cfif>
<!--- Note: we generally do a double query, one for max records and one for filtered results; if I didn't need to add in the author info, I could do the filtering and sorting
much more easily here versus in the QoQ --->
<cfquery name="qFilteredBooks" datasource="#variables.dsn#">
SELECT DISTINCT bookid, title, isbn, dateacquired, formats_formatid, genres_genreid, series_seriesid, series_position, amazon_asin,
formatlabel, genre, seriesname, NULL AS authors
FROM books INNER JOIN mylibrary_books_to_authors ON books.bookid = mylibrary_books_to_authors.books_bookid
INNER JOIN mylibrary_authors ON mylibrary_authors.authorid = mylibrary_books_to_authors.authors_authorid
</cfquery>
<cfloop query="qFilteredBooks">
<cfquery name="qAuthors" datasource="#variables.dsn#">
SELECT authorid, CONCAT(firstname, " ", lastname) AS authorname
FROM mylibrary_authors INNER JOIN mylibrary_books_to_authors ON mylibrary_authors.authorid = mylibrary_books_to_authors.authors_authorid
WHERE books_bookid = <cfqueryparam value="#bookid#" cfsqltype="cf_sql_integer" />
ORDER BY authorid
</cfquery>
<cfset myAuthors = "" />
<cfloop query="qAuthors">
<cfset myAuthors = ListAppend(myAuthors, '<a href="authorDetails.cfm?AuthorID=#authorid#">#qAuthors.authorname#</a>') />
</cfloop>
<cfset myAuthors = Replace(myAuthors, ",", ", ", "ALL") />
<cfset QuerySetCell(qFilteredBooks, "authors", myAuthors, currentRow) />
</cfloop>
<cfquery name="qFilteredBooks" dbtype="query">
SELECT bookid, title, dateacquired, formats_formatid, genres_genreid, series_seriesid, series_position,
formatlabel, genre, seriesname, authors
FROM qFilteredBooks
WHERE 1=1
<cfif Arguments.sSearch NEQ "">
<cfset var cleanSearchCriteria = TRIM(Arguments.sSearch) />
AND (
title LIKE <cfqueryparam value="%#cleanSearchCriteria#%" cfsqltype="cf_sql_varchar" />
OR formatlabel LIKE '%#cleanSearchCriteria#%'
OR genre LIKE '%#cleanSearchCriteria#%'
OR seriesname LIKE '%#cleanSearchCriteria#%'
OR authors LIKE '%#cleanSearchCriteria#%'
)
</cfif>
<cfif FilterOrderBy NEQ "">ORDER BY #FilterOrderBy#</cfif>
</cfquery>
<cfif Arguments.iDisplayLength NEQ -1>
<cfset startRow = Arguments.iDisplayStart + 1 />
<cfset maxRows = Arguments.iDisplayLength />
</cfif>
<!--- Loop our results, implementing our filtering and pagination and putting into an array to be returned --->
<cfoutput query="qFilteredBooks" startrow="#startRow#" maxrows="#maxRows#">
<cfset aGridData = arrayNew(1) />
<cfset arrayAppend(aGridData, "<a href='bookDetails.cfm?BookID=#qFilteredBooks.bookid#'>#qFilteredBooks.title#</a>") />
<cfset arrayAppend(aGridData, DateFormat(qFilteredBooks.dateacquired, 'mm/dd/yyyy')) />
<cfset arrayAppend(aGridData, "<a href='browse.cfm?GenreID=#genres_genreid#'>#qFilteredBooks.genre#</a>") />
<cfset arrayAppend(aGridData, TRIM(qFilteredBooks.authors)) />
<cfset arrayAppend(aGridData, "<a href='browse.cfm?FormatID=#qFilteredBooks.formats_formatid#'>#qFilteredBooks.formatlabel#</a>") />
<cfset arrayAppend(aGridData, "<a href='seriesDetails.cfm?SeriesID==#qFilteredBooks.series_seriesid#'>#qFilteredBooks.seriesname#</a>") />
<cfset ArrayAppend(aaData, aGridData) />
</cfoutput>
<cfset sGridReturn["sEcho"] = JavaCast("int", Arguments.sEcho) />
<cfset sGridReturn["iTotalRecords"] = qAllBooks.allRecordCount />
<cfset sGridReturn["iTotalDisplayRecords"] = qFilteredBooks.RecordCount />
<cfset sGridReturn["aaData"] = aaData />
<cfreturn sGridReturn />
</cffunction>
</cfcomponent>
Notice in this example, our code must deal with the pagination. As I put in my comments, because this particular example includes having to push more data into the query before processing, the pagination is handled by using startrow and maxrow in the output versus doing it in mySQL. If you don't need to do that, you can improve performance even more using a LIMIT in the filter query.
Also, this includes some very basic code for handling the filter requests. That part can be a bit more complicated if you want to mimic the way dataTables' filtering functions works when dealing with a regular table vs the Ajax/remote handling – particularly for the matching multiple columns. My example here just does a single column filter.
During the initial load and filtering, DataTables will show viewers a loading message while it's populating, versus just having an empty table there with no visible sign that something is happening. And unlike with the CFGRID, the links work and the users can clearly see what the links go to versus it being obscured by being JS powered.
So that's our second example of taking CFGRID and replacing it with DataTables. Again, I hope it shows why using DataTables is the better choice for its greater flexibility, more modern code, ease in updating, and larger "out-of-the-box" feature set. 🙂
I do plan to try to do a third part showing how to use dataTables to do an editable grid, but that one may take awhile as we're gearing up to start our PHP stuff soon (and as it isn't something we've done ourselves, I have to teach myself first LOL).