AJAX Pagination with Coldfusion and Jquery
In my continued fascination with Pagination (does that make me a Geek?) i am detailing here how to achieve it with AJAX (Jquery/JSON really) and Coldfusion. You can see a running example of the code here, and download all the code here. If you are interested in learning Jquery i recommend reading the book Learning Jquery.
Just to diverge slighly if you were like i was and really wanted to get to grips with AJAX. But got confused as to where to start due to the amount of different frameworks around, not to mention Coldfusion's own AJAX tools, like CFAjaxproxy. Then let me enlighten you. CFAjaxproxy is like a mini JS framework. So if you are wanting to use Jquery then i think you don't really need to worry about what CFajaxproxy does. I think Cfajaxproxy suits best if you need to get something done in a hurry and can't spare the time to learn Jquery.
Features
- Displays record set query data one page at a time.
- Page numbered links are also built and placed displayed records
- Allows for sorting of returned results by clicking on table headers.
- Includes highlighting of current page link and showing only up to 20 page numbers/links on page at any one time.
- CF returns only the records needed for each page (ie 10 at a time) rather than the whole record set.
- Has previous and next links at either end of page links.
The CFC
Each time the CFC is called from Jquery it returns a struct containing a result set of 10 records along with info about the archive being queried. Which can be used to help show user how many records they are paging through.
Accepts 2 parameters:
PageNumber - needed to help the CFC determine what records to return.
orderBy - passes in what database column to order the returned query by.
<cffunction name="pageInfo" displayname="pageInfo" access="remote" output="false" returnFormat="json">
<cfargument name="pageNumber" type="Numeric" required="true">
<cfargument name="orderBy" type="string" required="true">
<cfset var result = structNew()>
<CFIF not isDefined("arguments.pageNumber") >
<cfset arguments.pageNumber = 1>
</CFIF>
<cfquery datasource="which_new" name="myQuery">
select name, region, area, population from bbc
order by #arguments.orderBy#
<cfif #arguments.orderBy# is "population" or #arguments.orderBy# is "area">DESC</cfif>
</cfquery>
<cfset result.orderBy = #arguments.orderby#>
<!---determines how records are shown on one page --->
<cfset result.numberOfRecordsOnPage = 10>
<!---calculate the number of pages the archive will have --->
<cfset numberOfPagesNoCeil = (#myQuery.recordCount# / #result.numberOfRecordsOnPage#)>
<cfset result.numberOfPages = #ceiling(numberOfPagesNoCeil)#>
<!---here using url.pagenumber to work out what records to display on current page --->
<cfset result.from = ((#arguments.pageNumber# * #result.numberOfRecordsOnPage#) - #result.numberOfRecordsOnPage#) + 1>
<cfset result.to = (#arguments.pageNumber# * #result.numberOfRecordsOnPage#)>
<!---if on last page display the actual number of records in record set as the last to' figure'. Otherwise it gives
a false reading and gives the pagenumber * numberOfRecordsOnPage which is always a multiple of 10--->
<cfif #result.to# eq (#result.numberOfPages# * 10)>
<cfset result.to = myQuery.recordCount>
</cfif>
<!---Query to send back the 10 records relevant to pageNumber --->
<!--- Create a new 4-column query, specifying the column data types --->
<cfset recordsToGoBack = QueryNew("field1, field2, field3, field4", "VarChar, VarChar, VarChar, VarChar")>
<!--- --->
<cfloop query="myQuery" startrow="#result.from#" endrow="#result.TO#">
<!--- Set the values of the cells in the query --->
<cfset temp = QueryAddRow(recordsToGoBack)>
<cfset temp = QuerySetCell(recordsToGoBack, "field1", #name#)>
<cfset temp = QuerySetCell(recordsToGoBack, "field2", #region#)>
<cfset temp = QuerySetCell(recordsToGoBack, "field3", #area#)>
<cfset temp = QuerySetCell(recordsToGoBack, "field4", #population#)>
</cfloop>
<cfset result.recordsToGoBack = #recordsToGoBack#>
<cfreturn result/>
</cffunction>
</cfcomponent>
The JS (Jquery code) I am unable to display the Jquery code on my blog, as it come up all skew-whiff when i cut and paste via blog CFC admin. You can follow and view the JS code here. The code is mostly Jquery.
Displaying the records / Data
To communicate with the CFC i used Jquery's global function getJSON(). Then it was a matter of traversing the returned JSON object and using the values in there to help build a string containing a HTML table that displays the returned records. The HTML string need not be a table. It can be as simple as a
tag appended to the returned database values. Once i retrived and built my strings i then appended the strings to the appropriate divs on my .cfm page.
I came across a BIG gotcha when traversing the JSON. "THE JSON VARIABLES RETURNED FROM A CFC ALL SEEM TO BE IN UPPER CASE. AS JAVA SCRIPT IS CASE SENSITIVE REMEMBER TO REF THOSE VARIABLES IN UPPERCASE IN YOUR JS CODE OR THE CODE WILL FAIL"
Sorting
To achieve sorting i added a click handler to each table header. Which calls buildTable() with the appropriate orderBy column passed in, as well as the number of the current page. In my html string each table header cell's id is set with the relevant database column name. By doing this i can use this.id as a parameter in the function and the id of the dom element that the event originates from will be automatically passed into the function. In this case the id of each dom elemen (table header cell) will be a database column name, thereby allowing the recieving CFC to use this value in the order by clause of its sql code
Page Links
Building the page links meant looping for the numberOfPages in the archive, on each loop building a span tag for each page link, giving each tag a click handler that calls buildTable(pageNumber,sortByOrder) each time it is clicked, with the currentPage number and current sorting order as parameters. So that each time one of these links is clicked JS builds a new table with the appropriate records showing for the current page number, sorted in the order last selected by the user (or the default if none chosen). Changing the sort order while on a page will cause different records too be shown as you are reordering the record set.
Highlighting Current Page Link
By finding the span id of the current page (which is the value of the global pageNumber) i could simply use addClass() to highlight the selected link
Next and Previous Links
The next and previous links were created and added to the pager div. Each of them has an event handler that prevents the default event of hyperlink occuring, and calls buildTable() on each click. Finally i get a reference to the table that's built at the start, and insert the page links before and after the table (HTML String).
Finally i insert the page links both before and after HTML string (table).
The HTML on .cfm page
This is simply 2 divs that are used by the JS to update the page, and a css class to highlight the selected link.
<head>
<title>Jquery Pagination</title>
<script src="jquery.js" type="text/javascript"></script>
<script src="paginationRealData.js" type="text/javascript"></script>
<style type="text/css">
.selectedLink { background-color: #CCFFFF; font-family:Arial, Helvetica, sans-serif; font-size:12px; border: medium solid; border-color:#000066; border-width: thin }
.notSelectedLinks { font-family:Arial, Helvetica, sans-serif; font-size:12px; }
</style>
</head>
<body>
<div id="displayDiv" align="center"></div>
<div id="queryData" align="center"></div>
</body>
</html>
Works very nicely. It just needs a text filter and it's perfect! e.g. type in France and it will show all entries containing France - to save pagingating through lots of pages.