A little over six years ago I wrote a little blog post on how to do simple pagination in ColdFusion: ColdFusion and Pagination The post walked you through the simple step of iterating over a 'page' (or limited set) of data and providing links to navigate the data. For whatever reason, the post has gotten a few comments lately so I thought I'd post a quick update.
First off - Scott correctly pointed out that you should try your best to paginate in the database itself. I agreed - but to be fair - this example was meant for short sets of data that a user would actually peruse. I don't expect users to click "Next Page" over a few million records. That being said - you should try to offload as much as possible to the database and reduce the work your application server has to do.
Secondly - another reader, Ulises, asked if I could update my code to show the current page and the total number of pages. Both were rather easy. Let's look at the complete template and I'll explain how I added these features.
For the most part - this template is based on the code from the previous entry so I will not be covering every line. I did simplify the query a bit and used ColdFusion 10's simpler queryAddRow support.
The critical changes are these lines:
By using a simple bit of math, I can determine both what the total number of pages are as well as the current page. If I decide to change the size of a page later on the math will continue to work. And that's it. Not exactly rocket science, but the basic concept of paging over a set of data is still something folks like to see examples of, so I hope this is helpful! If you fancy seeing a demo, click the big obvious Demo button below...
Archived Comments
Good stuff, Ray. I love the thought of revisiting blog subjects.
FYI for those that don't want to write their own pagination code, I made an open source library, Pagination.cfc, that you can find at http://paginationcfc.riafor... and dopefly.com/projects/pagina.... It's worth a look!
Ray, great read.
As you pointed out, you don't expect users to paginate through millions of records. I know this is a bit off topic since you're talking about specific pagination algorithm, but just for the sake of discussion one of the things I've always pushed for in our projects is to give the user the ability to filter and reduce the amount of those records coming back as much as possible. Take basic searching on Google for example, no one paginates more than 3-5 pages without refining their search further.
Especially that now, six years later, we have much improved rich capabilities on the browser for this type of functionality.
The combination of good pagination code, coupled with a nice rich filtering feature is the ideal scenario for those providing this type of interface.
Happy Coding,
@Phill: One thing I'm curious about though is how often these advanced search forms get used. I mean shoot - even I barely use the advanced filtering options in Google. To be clear, I'm not saying they are bad - I'm just wondering how much they are actually used. On some of my sites I log searches. I should maybe look into logging regular searches versus others.
@Ray
Google search was perhaps a too broad example, but if you have a business app with a grid just simply giving your users a keyword search field that you can search against (say firstName, lastName, email columns) using ajax to live filter the recordset can help reduce the pain without adding much complexity.
Basically, my point is, don't rely on pagination alone. But a combination of pagination, filtering and even client side sorting (another great thing we can do now with HTML5) can make your app a lot more useful to busy users.
:)
@Phill: I agree with you. I'm just on a tangent now I think. I'd be curious to see metrics for complex forms in general.
@Ray,
Now I'm curious too, just afraid that searching "Google Advanced Search Usage Statistics" on Google may destroy the Universe. Wish me luck! ;)
Lately, I've been building result lists without paging. I use either a lazy loader (like facebook when you scroll down your news feed) or a button that says "Show More Results" that triggers an ajax request that appends new items to the list of results. I like this approach because you never lose the results you've already loaded. The downside is that you can't jump to a page... but that seems to me like a fairly random thing to do anyway. I don't think this approach is always the best, but it has worked for me in many situations when I used to page results.
I've been using DopeFly's Pagination.cfc... lots of options, styles & overrides. Good documentations and easy to add to any project.
http://www.dopefly.com/proj...
Believe it or not, I've had to do the filter a number of times on administrative screens where a user may have 10 or more pages of data to sift through, particularly on user accounts and some other, content-like apps.
Ray, you may want to check these out:
http://www.webveteran.com/b...
It has pagination and also page blockination. Yes, I just made up that word. Hope it catches on.
I used this the first time for one of my web apps here at work and I love it. With this update I will make it even better
Thanks a lot Raymond, you are great!! , my app looks better! :)
Thanks for updating this! It is very handy!
Just to expand on this a bit. I ended up taking this and using it with a CFDIV to make some nice AJAX paging for my search results. I thought that I'd point that out in case people reading this hadn't thought of it! It works so well and it was super easy!
Hi, I've just came around your post. I know it is a bit old but I hope I can get some help.
When I try your tutorial, I have a hard time getting to the next result page. Once I hit next page, I lost all my results. It seems like the query is empty, does that mean when the page is reload, the query did not create any results?
I feel like there is just one little thing I'm missing. Hope to hear from you. Thanks a lot.
In my example, I'm running a query (ok, fake query!) on every request. Are you doing that?
Hi Raymond,
After writing you, I replaced my query with your fake one and it works. So on my page, I ran a query at the top of the page. Now do I have to turn the output to a variable like you do (data)? If so, how do i do that, sorry I'm new to CF.
Well, I named my query data. You can name your query whatever you want, but pay attention to how my code assumes a query called that. If you call it beer, the code needs to change too.
Hi raymond,
So I have this at the beginning of my page:
<cfinvoke
component="CFC.interm"
method="SearchIntermByName"
returnvariable="SearchIntermByName">
<cfinvokeargument name="LastName" value="#FORM.LastName#"/>
<cfinvokeargument name="FirstName" value="#FORM.FirstName#"/>
</cfinvoke>
What I have done is that where ever you called "data", I just called my query "SearchIntermByName" but my question is since your query is being store as a variable, do I have to do so as well? It seems like, when the page is refresh, all the results are gone.
You *are* storing your result as a variable - SearchIntermByName.
My demo links to itself, which means as you paginate, you keep running the same code, and you get the same query, etc time.
yah i though so too so i am not sure what I've done wrong...
Ok, so look at the CFM you are using. When you click to go to page 2, it *is* running again, right?
okay, I know what it is, it's the FORM variables. They are gone on the second time. I will have to find a way to store them somewhere
Session scope may work for you. :)
YAH, it's working! thanks so much for your help Raymond.