ColdFusion and Pagination - Part 2

coldfusion (This post is more than 2 years old.)

Before getting into this entry, let me share two quick notes about the last entry. First off, if you are a subscriber and only read the emails, I had made a small mistake in my logic. It was corrected on the entry. Thanks to my reader Fernando for pointing it out. Secondly, Rob Gonda made the point that you probably don't want to do pagination in ColdFusion if you are returning a hundred thousand or so results. He is absolutely correct and shows a better alternative here.

In the first entry, I demonstrated how you can use ColdFusion to do simple pagination. I had a query and a variable determining how many records to show per page. I showed how you could make links to let the user go back and forward one page a time. This works fine for a small set of results, but if you have more results, a user will have to hit "Next Page" a few too many times. What if you could simply provide a list of pages so a user could immediately jump to the page? It is really rather simple and I will show you how. Before going on though, be sure you read the last entry. (Boy, I sure wish BlogCFC support related entries. Oh wait, it does!.)

Let me start by showing the code, and then explaining what I did. At the end of the entry I'll post the complete code.

<cfset page = 0> <cfloop index="x" from="1" to="#data.recordCount#" step="#perpage#"> <cfset page = page + 1> <cfif url.start is not x> <cfset link = cgi.script_name & "?start=" & x> <cfoutput><a href="#link#">#page#</a></cfoutput> <cfelse> <cfoutput>#page#</cfoutput> </cfif> </cfloop>

The first thing I do is create a variable that will store the page number. This isn't actually needed as I could use math, but I've learned not to use math before I've had enough coffee.

Next I do a loop from one to the record count of the query. However, I use a step value of perpage. This makes it easy for me to split up my data into the right segments.

Inside the loop I increment page by one. Then I check to see if our current starting position is equal to X. If it isn't, it means I'm not on that page. I make a link (just like I did for the previous and next links) and just use X for my starting position. If not, I simply output X.

That's it. Now I have both a previous and next link, and between them, a list of pages for direct access to a particular page. Here is the complete code.

<cfset data = queryNew("id,name,age,active","integer,varchar,integer,bit")>

<cfloop index="x" from="1" to="31"> <cfset queryAddRow(data)> <cfset querySetCell(data,"id",x)> <cfset querySetCell(data,"name","User #x#")> <cfset querySetCell(data,"age",randRange(20,90))> <cfset querySetCell(data,"active",false)> </cfloop>

<cfset perpage = 10> <cfparam name="url.start" default="1"> <cfif not isNumeric(url.start) or url.start lt 1 or url.start gt data.recordCount or round(url.start) neq url.start> <cfset url.start = 1> </cfif>

<h2>Random People</h2>

<cfoutput query="data" startrow="#url.start#" maxrows="#perpage#"> #currentrow#) #name#<br /> </cfoutput>

<p align="right"> [ <cfif url.start gt 1> <cfset link = cgi.script_name & "?start=" & (url.start - perpage)> <cfoutput><a href="#link#">Previous Page</a></cfoutput> <cfelse> Previous Page </cfif>

<cfset page = 0> <cfloop index="x" from="1" to="#data.recordCount#" step="#perpage#"> <cfset page = page + 1> <cfif url.start is not x> <cfset link = cgi.script_name & "?start=" & x> <cfoutput><a href="#link#">#page#</a></cfoutput> <cfelse> <cfoutput>#page#</cfoutput> </cfif> </cfloop>

<cfif (url.start + perpage - 1) lt data.recordCount> <cfset link = cgi.script_name & "?start=" & (url.start + perpage)> <cfoutput><a href="#link#">Next Page</a></cfoutput> <cfelse> Next Page </cfif> ] </p>

Hire Me!

I'm currently looking for my next role in developer evangelism and advocacy. I have a long history of helping companies work with developers and love to write, create demos, and present at conferences. You can find my resume to learn more and drop me an email (raymondcamden@gmail.com) to reach out.

Support this Content!

If you like this content, please consider supporting me. You can become a Patron, visit my Amazon wishlist, or buy me a coffee! Any support helps!

Want to get a copy of every new post? Use the form below to sign up for my newsletter.

Archived Comments

Comment 1 by Neil Middleton posted on 4/26/2006 at 4:26 PM

Unlike you to make a fix to something the day after release Ray....

Comment 2 by Raymond Camden posted on 4/26/2006 at 5:14 PM

I know. What's up with that? I never make mistakes.

Comment 3 by Edward T posted on 4/26/2006 at 5:33 PM

> ...I've learned not to use math before I've had enough coffee.

"I don't do math in public." - overheard on NPR; it has become my stock response when someone asks me to do arithmetic in a meeting.

A possible solution that sort of lies midway between this solution and Rob's is using a caching parameter in the query. It speeds up retrieval, and as long as you don't have too much change in the data or too many rows to retrieve, you get the performance boost with the cf pagination.

Comment 4 by Rob Gonda posted on 4/26/2006 at 5:59 PM

I thought about that too, but until cfmx7, query of queries has been buddy with datatypes...

Now with cfmx7, I wanted to find a medium not to cache the entire table, because again, my table weights about 10Gb :) Downloading 1000 records at a time and caching them in CF, and use CF pagination for smaller blocks could work ... but I find that to be too much complication.

Comment 5 by Tom Mollerus posted on 4/26/2006 at 6:09 PM

One solution I've used in the past is to run two queries, each using the exact same joins, where clauses, grouping, and ordering. The only difference is that the first query is cached and returns only one column, whereas the second query returns the rows corresponding to the values in the first query that match the "page" you're viewing (e.g., rows 51 - 100 representing the second page of a 50-rows-per-page pagination).

It's definitely a complicated solution, but it works well to pull only a few records from the db in what could otherwise be a massive query.

Comment 6 by Edward T posted on 4/26/2006 at 6:11 PM

Rob, that's a sweet idea...one way you might implement it is by having CF do the loading in the background. I did a site not too long ago that had to pull medical provider info for multiple insurance plans. I didn't know which provider they would start with (depends on which page they enter the site through), but I wanted them to have every provider's page ready after entering the zip code any any entry page.

So, I stored the data for each user's zip in session, and then had CF GET the provider's list. When the page rendered, I had CF decide which providers were left to get, write a little JavaScript in the page to do an AJAX load of the remaining session objects (one per provider). When the user went to the next page, CF checked for a populated object and did the GET itself if the AJAX failed. Otherwise, voila, the object is already there. It's really simple AJAX, since there is nothing to render - it's just doing an asyn load in the background.

Anyhow, you could simplify block caching in this way. You work with the current 1,000 record set until the user pages up near the end of the block, then AJAX-call the next block into cache. It is a little more complex, but you can use the same stored proc and just let CF decide what the page limits should be.

Comment 7 by Raymond Camden posted on 4/26/2006 at 6:13 PM

Tom, I've done that as well. Our CMS uses it for pagination in the admin.

Comment 8 by Neil Middleton posted on 4/26/2006 at 6:20 PM

Another option lies in SQL2005 where it has query pagination built in..

<code>
SELECT
myField1
, myField2
, RowID
FROM
(
SELECT
myField1
, myField2
, ROW_NUMBER() OVER ( ORDER BY myField1, myField2 ) AS RowID
FROM
myHugeAndIncomprehensibleTablesAndJoins
WHERE
AnyFiltersIWant = 1
) myPageQuery
WHERE
RowID BETWEEN 21 AND 30
ORDER BY
RowID
</code>

Comment 9 by Edward T posted on 4/26/2006 at 6:24 PM

Neil,

Yes, or use rownum in Oracle, but you still need to hit the db and pull the data across the network. With caching, the query is right there in CF memory.

Comment 10 by Scott Stroz posted on 4/26/2006 at 7:38 PM

MySQL has LIMIT to do the same.

Comment 11 by Donnovan Lewis posted on 4/27/2006 at 11:57 PM

So its interesting to randomly come to Rays site and see the new things in discussion. It just so happened that i was bored one day and wanted to set up a db for myself and my friends to look at and search my music. (Before anyone goes all anti-pirate on me, there is no access to the actual music. It is just a listing of what i have.)

I have seen some interesting ways to do paging of results but this one caught my eye. It is the ability do display the pages in "chunks" as i call it. I chose 3 page chunking. This allows the first 3 pages and last 3 pages always to be visible and then the rest are in a "..." filler. The current page would show 2 previous pages and 2 next pages in the page listing. so for example it would be this link: http://wilsondesigns.ath.cx...

I would explain it but seeing it is easier. Now it took me a couple hours to figure out the algorithm for this, but i am not sure if there was a better way to do it. If there isnt then the code is below for anyone to use.

<!--- Begin: Code --->
<cfparam name="page" default="0">
<cfparam name="pageChunk" default="3">
<cfparam name="perPage" default="50">
<cfparam name="searchCriteria" default="">

<cfquery>QUERY CODE HERE</cfquery>

<cfset startRow = (page*perPage)+1>
<cfset endRow = (startRow+perPage)-1>
<cfif endRow GT searchdb.recordCount>
<cfset endRow = searchdb.recordCount>
</cfif>
<cfset numPages = Ceiling(searchdb.recordCount/perPage)>
<cfset pagesLeft = numPages - pageChunk>
<cfif pagesLeft GT pageChunk>
<cfset endFrom = numPages-pageChunk>
<cfelse>
<cfset endFrom = numPages-pagesLeft>
</cfif>

<!--- Begin: Next Previous --->
<cfoutput>
<cfif page GT 0>
<a onfocus="this.blur();" href="?page=#page-1#&searchCriteria=#searchCriteria#">&lt;&lt; Prev</a>
<cfelse>
&lt;&lt; Prev
</cfif>
&nbsp;&nbsp;&nbsp;
<cfif page LT numPages-1>
<a onfocus="this.blur();" href="?page=#page+1#&searchCriteria=#searchCriteria#">Next &gt;&gt;</a>
<cfelse>
Next &gt;&gt;
</cfif>
</cfoutput>
<!--- End: Next Previous --->

<!--- Begin: Page Numbering --->
<cfoutput>
<!--- Begin: FirstChunk --->
<cfif numPages GT pageChunk>
<cfloop from="0" to="#pageChunk-1#" index="i">
<cfset sRow = (i*perPage)+1>
<cfset eRow = (sRow+perPage)-1>
<cfif eRow GT searchdb.recordCount>
<cfset eRow = searchdb.recordCount>
</cfif>
<cfif i NEQ page>
<cfif i GT 0>
|
</cfif>
<a onfocus="this.blur();" href="?page=#i#&searchCriteria=#searchCriteria#">#sRow#-#eRow#</a>
<cfelse>
<cfif i GT 0>
|
</cfif>
#sRow#-#eRow#
</cfif>
</cfloop>
<!--- End: FirstChunk --->
<!--- Begin: MiddleChunk --->
<cfif numPages GT 2*pageChunk>
<cfif page-pageChunk GTE pageChunk>
| ...
</cfif>
<cfset midTo = page+(pageChunk-1)>
<cfif midTo GTE endFrom>
<cfset midTo = endFrom-1>
</cfif>
<cfif midTo GTE pageChunk>
<cfset midFrom = page-(pageChunk-1)>
<cfif midFrom LTE pageChunk>
<cfset midFrom = pageChunk>
</cfif>
<cfif midFrom LT endFrom>
<cfloop from="#midFrom#" to="#midTo#" index="i">
<cfset sRow = (i*perPage)+1>
<cfset eRow = (sRow+perPage)-1>
<cfif eRow GT searchdb.recordCount>
<cfset eRow = searchdb.recordCount>
</cfif>
<cfif i NEQ page>
| <a onfocus="this.blur();" href="?page=#i#&searchCriteria=#searchCriteria#">#sRow#-#eRow#</a>
<cfelse>
| #sRow#-#eRow#
</cfif>
</cfloop>
</cfif>
</cfif>
<cfif endFrom-page GT pageChunk>
| ...
</cfif>
</cfif>
<!--- End: MiddleChunk --->
<!--- Begin: LastChunk --->
<cfloop from="#endFrom#" to="#numPages-1#" index="i">
<cfset sRow = (i*perPage)+1>
<cfset eRow = (sRow+perPage)-1>
<cfif eRow GT searchdb.recordCount>
<cfset eRow = searchdb.recordCount>
</cfif>
<cfif i NEQ page>
| <a onfocus="this.blur();" href="?page=#i#&searchCriteria=#searchCriteria#">#sRow#-#eRow#</a>
<cfelse>
| #sRow#-#eRow#
</cfif>
</cfloop>
<!--- End: LastChunk --->
<cfelse>
<!--- Begin: One Chunk Case --->
<cfloop from="0" to="#numPages-1#" index="i">
<cfset sRow = (i*perPage)+1>
<cfset eRow = (sRow+perPage)-1>
<cfif eRow GT searchdb.recordCount>
<cfset eRow = searchdb.recordCount>
</cfif>
<cfif i NEQ page>
<cfif i GT 0>
|
</cfif>
<a onfocus="this.blur();" href="?page=#i#&searchCriteria=#searchCriteria#">#sRow#-#eRow#</a>
<cfelse>
<cfif i GT 0>
|
</cfif>
#sRow#-#eRow#
</cfif>
</cfloop>
<!--- End: One Chunk Case --->
</cfif>
</cfoutput>
<!--- End: Page Numbering --->

Comment 12 by Donnovan Lewis posted on 4/27/2006 at 11:59 PM

wow that looks hideous...how can i make that code look nicer in with tabs?

Comment 13 by Justice posted on 4/28/2006 at 10:40 PM

Raymond, I think you forgot one part in your StartRow sanitation, how about 'not structKeyExists(URL, "start")' ?

Comment 14 by Raymond Camden posted on 4/28/2006 at 10:44 PM

No, I have a cfparam above it.

Comment 15 by Jeff Gladnick posted on 7/11/2006 at 5:27 PM

I am currently in the hell-hole that is pagination in SQL-Server 2000, and its driving me bonkers to no end.

I am trying to return 50 results per page, and I just can't get the SQL to return stuff in the correct order, or to return the right data.

I started off with a variation of MSDN's official suggestion:
(link: http://msdn.microsoft.com/l...

---------
SELECT TOP <pageSize> CustomerID,CompanyName,ContactName,ContactTitle
FROM
(SELECT TOP <currentPageNumber * pageSize>
CustomerID,CompanyName,ContactName,ContactTitle
FROM
Customers AS T1 ORDER BY ContactName DESC)
AS T2 ORDER BY ContactName ASC
---------

That kind of works - except it starts at the end, with the Z's. I kind of got this to work, but wrapping this in yet ANOTHER SELECT top 50 order by statement. So now there are 3 levels, and i know for sure one of them is redundant. I was frustrated enough that i was willing to accept this and be done with it, except i noticed im not getting accurate results for some reason.

So my question for the group - can someone recommend a better nested SQL query, or a different way BESIDES using a stored procedure?

Comment 16 by Jeff Gladnick, Produce Marketi posted on 7/11/2006 at 6:14 PM

Here is the actual SQL code i am using:

SELECT *
FROM (SELECT TOP 50 *
FROM (SELECT TOP 150 first_name, last_name, label_name
FROM USR_WEB_CONTACTS
WHERE 1 = 1 AND (Last_Name LIKE '%smith%' OR
first_name LIKE '%smith%')
ORDER BY Last_Name ASC) DERIVEDTBL
ORDER BY Last_Name DESC) DERIVEDTBL
ORDER BY last_name

-----
I have noticed that when i add columns to the list (first_name, last_name, label_name) it starts giving me back a different set of data. which i find very surprising - how could this happen?

Comment 17 by William Haun posted on 1/9/2007 at 1:01 AM

I was using Ray's fabulous pagination code and ran into this lovely IE bug (err... feature).

Here was my url created for the next set of results:

mysite.com/page.cfm?start=1...

It worked beautifully in Mozilla, Opera, and Safari but didn't in IE. Internet Explorer rendered the link as:

mysite.com/page.cfm?start=10°_id=15

It turns out there is a special HTML code for the degree sign. The code is &deg; IE is assuming that I forgot the semi-colon and is plugging the special character in for me.

Thanks but no thanks IE. Guess I'll have to move my deg_id variable to the start of the query string so that it shows up after the question mark. Either that or change the variable name.

Comment 18 by Raymond Camden posted on 1/9/2007 at 4:47 AM

I'd just change the variable name.