Ben Nadel
On User Experience (UX) Design, JavaScript, ColdFusion, Node.js, Life, and Love.
I am the chief technical officer at InVision App, Inc - a prototyping and collaboration platform for designers, built by designers. I also rock out in JavaScript and ColdFusion 24x7.
Meanwhile on Twitter
Loading latest tweet...
Ben Nadel at cf.Objective() 2014 (Bloomington, MN) with:

Oh Baby, It's So Hot When You Paginate! (aka Paginating Results In ColdFusion)

By Ben Nadel on
Tags: ColdFusion

My web log results pages are now paginating. I have been meaning to get around to it, but have been too busy. Yesterday, Ray Camden pointed out that on pages with a LOT of entries, my code blocks were coming up all grey. I am not 100% sure, but I think the page was having trouble rendering because there was SO much information to display. This prompted me to get off my butt and paginate. Hopefully this has fixed that today.

I thought I would take this time to explain how I perform pagination as I know it is never an easy task in ColdFusion (or any other language for that matter). Over the years I have tried many different methods for pagination. Some were good, some were complete crap. What I have now seems to work the best of all things I have tried.

Before I dive into it, let me get out a few things that I consider when working on pagination:

  1. I want pagination to be done in ColdFusion, NOT on the SQL server. I don't like putting a lot of complex stuff on the SQL server. ColdFusion is much more flexible and easier to work with.
  2. I don't want to return too much excess data from the database. I used to return massive queries and then just output 10 results. That meant I was only using a fraction of what I returned. This was bad for processing time and data transfer.
  3. I don't want to repeat my logic too much.

That being said, let me quickly just outline my pagination, then I will go into detail about the individual steps:

  1. Set up default pagination object.
  2. Query the database for result set IDs.
  3. Update pagination object with ID list.
  4. Query the database for complete information for subset of ID list.

That's the basic idea. Now, let's get down and dirty with some detail information. The player in the game is my pagination object. I start off by setting up default values for it:

  • <!---
  • Create the pager object that we will use to calculate
  • where we are in pagination and where we can go. Set up the
  • variables that we will need to be passed from onepage to another.
  • --->
  • <cfset REQUEST.Pager = APPLICATION.ServiceFactory.GetDataPager() />
  • <cfset REQUEST.Pager.SetOffset( REQUEST.Attributes.search_offset ) />
  • <cfset REQUEST.Pager.SetPageSize( 10 ) />
  • <cfset REQUEST.Pager.SetUrl(
  • REQUEST.Environment.Web.ScriptName &
  • "?search_offset=[OFFSET]"
  • ) />

First, I get the a new instance of the DataPager.cfc from my service factory. The service factory is just returning a CreateObject() call combined with the proper Init() method. Then I set my page offset. This either defaults to zero (or one - I can never remember what the proper thing is) or is based on what is passed via the URL / FORM object. Then, I set the page size of the pagination results (in this case 10 items per page). Then, I set up the URL for all the pagination links. The paginator uses a constant [OFFSET] to dynamically create url's when outputting the pages and the previous / next links.

So, regarding the [OFFSET], if we take the above example, where the script name is "blog-entries.htm", the static url would be:

  • /blog-entries.htm?search_offset=[OFFSET]

... and the dynamic urls for pages 1 - 4 would be:

/blog-entries.htm?search_offset=1
/blog-entries.htm?search_offset=11
/blog-entries.htm?search_offset=21
/blog-entries.htm?search_offset=31

As you can see, the pager automatically substitutes the proper offset when outputting the urls.

I think now would be a good time to explore what is really going on under the pagination object. Here is a list of the methods available in the object (public and private):

Init()
Calculate()
GetBucket()
GetBucketEndPage()
GetBuckets()
GetBucketSize()
GetBucketStartPage()
GetEnd()
GetNext()
GetOffset()
GetPage()
GetPages()
GetPageSize()
GetPrev()
GetRecordCount()
GetStart()
GetURL()
HasMultiplePages()
ReplaceOffset()
SetBucketSize()
SetOffset()
SetPageSize()
SetRecordCount()
SetUrl()

And, here is the code for the DataPager.cfc (I will be adding this to snippets later):

  • <cfcomponent
  • displayname="DataPager"
  • output="no"
  • hint="Handles data pagination mathematics.">
  •  
  • <!--- Run the pseudo constructor to set up default data structures. --->
  • <cfscript>
  •  
  • // Set up an instance structure to hold instance data.
  • VARIABLES.Instance = StructNew();
  •  
  • // Set default instance data.
  • VARIABLES.Instance.Page = 0;
  • VARIABLES.Instance.Pages = 0;
  • VARIABLES.Instance.PageSize = 15;
  • VARIABLES.Instance.Bucket = 0;
  • VARIABLES.Instance.Buckets = 0;
  • VARIABLES.Instance.BucketSize = 10;
  • VARIABLES.Instance.BucketStartPage = 0;
  • VARIABLES.Instance.BucketEndPage = 0;
  • VARIABLES.Instance.RecordCount = 0;
  • VARIABLES.Instance.Offset = 0;
  • VARIABLES.Instance.Prev = 0;
  • VARIABLES.Instance.Next = 0;
  • VARIABLES.Instance.End = 0;
  • VARIABLES.Instance.Start = 0;
  • VARIABLES.Instance.URL = "";
  •  
  • </cfscript>
  •  
  •  
  • <cffunction name="Init" access="public" returntype="DataPager" output="no"
  • hint="Returns an initialized data pager instance.">
  •  
  • <!--- REturn this reference. --->
  • <cfreturn THIS />
  • </cffunction>
  •  
  •  
  • <cffunction name="Calculate" access="public" returntype="boolean" output="no"
  • hint="Calculates the page instance data based on the parts that were manually set.">
  •  
  • <cfscript>
  •  
  • // Check properites for valid values and ranges. If any of the values is invalid,
  • // set the default or most appropriate value.
  •  
  • if (VARIABLES.Instance.PageSize LT 1){
  • VARIABLES.Instance.PageSize = 15;
  • }
  •  
  • if (VARIABLES.Instance.BucketSize LT 1){
  • VARIABLES.Instance.BucketSize = 5;
  • }
  •  
  • if (VARIABLES.Instance.Offset GT VARIABLES.Instance.RecordCount){
  • VARIABLES.Instance.Offset = VARIABLES.Instance.RecordCount;
  • }
  •  
  • if (VARIABLES.Instance.Offset LT 1){
  • VARIABLES.Instance.Offset = 1;
  • }
  •  
  •  
  • // Only update the list page if we have a record count to work with. Otherwise,
  • // just return default values.
  • if (VARIABLES.Instance.RecordCount GT 0){
  •  
  • // Figure out how many total pages.
  • VARIABLES.Instance.Pages = Ceiling(VARIABLES.Instance.RecordCount / VARIABLES.Instance.PageSize);
  •  
  • // Figure out which page we are on.
  • VARIABLES.Instance.Page = Ceiling(VARIABLES.Instance.Offset / VARIABLES.Instance.PageSize);
  •  
  • // Figure out how many total buckets exist.
  • VARIABLES.Instance.Buckets = Ceiling(VARIABLES.Instance.Pages / VARIABLES.Instance.BucketSize);
  •  
  • // Figure out which bucket we are in.
  • VARIABLES.Instance.Bucket = Ceiling(VARIABLES.Instance.Page / VARIABLES.Instance.BucketSize);
  •  
  • // Get bucket start page.
  • VARIABLES.Instance.BucketStartPage = (((VARIABLES.Instance.Bucket - 1) * VARIABLES.Instance.BucketSize) + 1);
  •  
  • // Get bucket end page.
  • VARIABLES.Instance.BucketEndPage = (VARIABLES.Instance.BucketStartPage + VARIABLES.Instance.BucketSize - 1);
  •  
  • // Validate end bucket range.
  • if (VARIABLES.Instance.BucketEndPage GT VARIABLES.Instance.Pages){
  • VARIABLES.Instance.BucketEndPage = VARIABLES.Instance.Pages;
  • }
  •  
  •  
  • // Readjust the current offset by page. ie. Make the current offset the first item
  • // on the current page.
  • VARIABLES.Instance.Offset = (((VARIABLES.Instance.Page - 1) * VARIABLES.Instance.PageSize) + 1);
  •  
  • // Calculate Prev Link.
  • if (VARIABLES.Instance.Page GT 1){
  • VARIABLES.Instance.Prev = (VARIABLES.Instance.Offset - VARIABLES.Instance.PageSize);
  • }
  •  
  • // Calculate Next Link.
  • if (VARIABLES.Instance.Page LT VARIABLES.Instance.Pages){
  • VARIABLES.Instance.Next = (VARIABLES.Instance.Offset + VARIABLES.Instance.PageSize);
  • }
  •  
  • // Set up start and end values.
  • VARIABLES.Instance.Start = VARIABLES.Instance.Offset;
  • VARIABLES.Instance.End = (VARIABLES.Instance.Offset + VARIABLES.Instance.PageSize - 1);
  •  
  • // Validate the end row.
  • if (VARIABLES.Instance.End GT VARIABLES.Instance.RecordCount){
  • VARIABLES.Instance.End = VARIABLES.Instance.RecordCount;
  • }
  •  
  • }
  •  
  • // Return out.
  • return(true);
  •  
  • </cfscript>
  • </cffunction>
  •  
  •  
  • <cffunction name="GetBucket" access="public" returntype="numeric" output="no"
  • hint="Gets the current bucket.">
  •  
  • <cfreturn VARIABLES.Instance.Bucket />
  • </cffunction>
  •  
  •  
  • <cffunction name="GetBucketEndPage" access="public" returntype="numeric" output="no"
  • hint="Gets the last page in the current bucket.">
  •  
  • <cfreturn VARIABLES.Instance.BucketEndPage />
  • </cffunction>
  •  
  •  
  • <cffunction name="GetBuckets" access="public" returntype="numeric" output="no"
  • hint="Gets the number of buckets in the list pager.">
  •  
  • <cfreturn VARIABLES.Instance.Buckets />
  • </cffunction>
  •  
  •  
  • <cffunction name="GetBucketSize" access="public" returntype="numeric" output="no"
  • hint="Gets the bucket size.">
  •  
  • <cfreturn VARIABLES.Instance.BucketSize />
  • </cffunction>
  •  
  •  
  • <cffunction name="GetBucketStartPage" access="public" returntype="numeric" output="no"
  • hint="Gets the first page in the current bucket.">
  •  
  • <cfreturn VARIABLES.Instance.BucketStartPage />
  • </cffunction>
  •  
  •  
  • <cffunction name="GetEnd" access="public" returntype="numeric" output="no"
  • hint="Gets the last offset in the current page.">
  •  
  • <cfreturn VARIABLES.Instance.End />
  • </cffunction>
  •  
  •  
  • <cffunction name="GetNext" access="public" returntype="numeric" output="no"
  • hint="Gets the next page.">
  •  
  • <cfreturn VARIABLES.Instance.Next />
  • </cffunction>
  •  
  •  
  • <cffunction name="GetOffset" access="public" returntype="numeric" output="no"
  • hint="Gets the current offset">
  •  
  • <cfreturn VARIABLES.Instance.Offset />
  • </cffunction>
  •  
  •  
  • <cffunction name="GetPage" access="public" returntype="numeric" output="no"
  • hint="Gets the current page.">
  •  
  • <cfreturn VARIABLES.Instance.Page />
  • </cffunction>
  •  
  •  
  • <cffunction name="GetPages" access="public" returntype="numeric" output="no"
  • hint="Gets the number of pages in the list pager.">
  •  
  • <cfreturn VARIABLES.Instance.Pages />
  • </cffunction>
  •  
  •  
  • <cffunction name="GetPageSize" access="public" returntype="numeric" output="no"
  • hint="Gets the page size.">
  •  
  • <cfreturn VARIABLES.Instance.PageSize />
  • </cffunction>
  •  
  •  
  • <cffunction name="GetPrev" access="public" returntype="numeric" output="no"
  • hint="Gets the previous page.">
  •  
  • <cfreturn VARIABLES.Instance.Prev />
  • </cffunction>
  •  
  •  
  • <cffunction name="GetRecordCount" access="public" returntype="numeric" output="no"
  • hint="Gets the record count.">
  •  
  • <cfreturn VARIABLES.Instance.RecordCount />
  • </cffunction>
  •  
  •  
  • <cffunction name="GetStart" access="public" returntype="numeric" output="no"
  • hint="Gets the first offset in the current page.">
  •  
  • <cfreturn VARIABLES.Instance.Start />
  • </cffunction>
  •  
  •  
  • <cffunction name="GetURL" access="public" returntype="numeric" output="no"
  • hint="Gets the base url.">
  •  
  • <cfreturn VARIABLES.Instance.URL />
  • </cffunction>
  •  
  •  
  • <cffunction name="HasMultiplePages" access="public" returntype="boolean" output="no"
  • hint="Checks to see if the page had more than one page to display.">
  •  
  • <cfreturn (VARIABLES.Instance.Pages GT 1) />
  • </cffunction>
  •  
  •  
  • <cffunction name="ReplaceOffset" access="public" returntype="string" output="no"
  • hint="Replaces the [OFFSET] with the passed offset in the base url. This gives the user more flexibility as to how the offset is going to be used.">
  •  
  • <!--- Define arguments. --->
  • <cfargument name="Offset" type="numeric" required="yes" />
  •  
  • <!--- Return the replaced url. --->
  • <cfreturn ReplaceNoCase(VARIABLES.Instance.URL, "[OFFSET]", ARGUMENTS.Offset, "ALL") />
  • </cffunction>
  •  
  •  
  • <cffunction name="SetBucketSize" access="public" returntype="boolean" output="no"
  • hint="Sets the bucket size (pages per bucket).">
  •  
  • <!--- Define arguments. --->
  • <cfargument name="BucketSize" type="numeric" required="yes" />
  •  
  • <cfset VARIABLES.Instance.BucketSize = ARGUMENTS.BucketSize />
  • <cfreturn true />
  • </cffunction>
  •  
  •  
  • <cffunction name="SetOffset" access="public" returntype="boolean" output="no"
  • hint="Sets the page offset.">
  •  
  • <!--- Define arguments. --->
  • <cfargument name="Offset" type="numeric" required="yes" />
  •  
  • <cfset VARIABLES.Instance.Offset = ARGUMENTS.Offset />
  • <cfreturn true />
  • </cffunction>
  •  
  •  
  • <cffunction name="SetPageSize" access="public" returntype="boolean" output="no"
  • hint="Sets the page size.">
  •  
  • <!--- Define arguments. --->
  • <cfargument name="PageSize" type="numeric" required="yes" />
  •  
  • <cfset VARIABLES.Instance.PageSize = ARGUMENTS.PageSize />
  • <cfreturn true />
  • </cffunction>
  •  
  •  
  • <cffunction name="SetRecordCount" access="public" returntype="boolean" output="no"
  • hint="Sets the record count.">
  •  
  • <!--- Define arguments. --->
  • <cfargument name="RecordCount" type="numeric" required="yes" />
  •  
  • <cfset VARIABLES.Instance.RecordCount = ARGUMENTS.RecordCount />
  • <cfreturn true />
  • </cffunction>
  •  
  •  
  • <cffunction name="SetUrl" access="public" returntype="boolean" output="no"
  • hint="Sets the base url.">
  •  
  • <!--- Define arguments. --->
  • <cfargument name="Url" type="string" required="yes" />
  •  
  • <cfset VARIABLES.Instance.Url = ARGUMENTS.Url />
  • <cfreturn true />
  • </cffunction>
  •  
  • </cfcomponent>

I haven't updated this code in a bit, so it doesn't quite hold up to my new standards, but that is personal. If you take a quick look though, you will see that this object does a fair amount of validation to make sure that only valid pagination items get returned.

Now, let's get into the first database hit: getting the full set of IDs. This is the only part that returns extra data. If our search criteria matches 500 records, then we are returning 500 IDs (even if we are only outputting 10). I know this is lame, but it's the best way I could figure out how to perform and validation pagination in ColdFusion while duplicating the least amount logic and data returns.

Let's look at the query:

  • <!--- Query for IDs. --->
  • <cfquery name="qID" datasource="...">
  • SELECT
  • b.id
  • FROM
  • blog_entry b
  • INNER JOIN
  • display_status d
  • ON
  • (
  • b.display_status_id = d.id
  • AND
  • d.is_active = 1
  • AND
  • d.is_listed = 1
  •  
  • <!--- Check for search year. --->
  • <cfif REQUEST.Attributes.search_year>
  • AND
  • b.date_posted >= <cfqueryparam value="#CreateDate(REQUEST.Attributes.search_year, 1, 1)#" cfsqltype="CF_SQL_TIMESTAMP" />
  • AND
  • b.date_posted < <cfqueryparam value="#CreateDate((REQUEST.Attributes.search_year + 1), 1, 1)#" cfsqltype="CF_SQL_TIMESTAMP" />
  • </cfif>
  •  
  • <!--- Check for search month. --->
  • <cfif (REQUEST.Attributes.search_year AND REQUEST.Attributes.search_month)>
  • AND
  • b.date_posted >= <cfqueryparam value="#CreateDate(REQUEST.Attributes.search_year, REQUEST.Attributes.search_month, 1)#" cfsqltype="CF_SQL_TIMESTAMP" />
  • AND
  • b.date_posted < <cfqueryparam value="#DateAdd('m', 1, CreateDate(REQUEST.Attributes.search_year, REQUEST.Attributes.search_month, 1))#" cfsqltype="CF_SQL_TIMESTAMP" />
  • </cfif>
  •  
  • <!--- Check for tag search. --->
  • <cfif REQUEST.Attributes.search_tag_id>
  • AND
  • b.id IN
  • (
  • SELECT
  • btjn.blog_entry_id
  • FROM
  • blog_entry_tag_jn btjn
  • WHERE
  • btjn.tag_id = <cfqueryparam value="#REQUEST.Attributes.search_tag_id#" cfsqltype="CF_SQL_INTEGER" />
  • )
  • </cfif>
  • )
  • ORDER BY
  • b.date_posted DESC,
  • b.time_posted DESC,
  • b.id DESC
  • </cfquery>

As you can see here, I using all the search criteria (years, months, tag ids) but I am ONLY returning ID's of the blog entries. You will also notice that I am returning the IDs in proper ORDER BY fashion. This will come into play later on, so keep it in the back of your mind.

Now that we have the default paginator and the ID list, I update the pagination object:

  • <!--- Update the pager based on the record count and then recalculate. --->
  • <cfset REQUEST.Pager.SetRecordCount( qID.RecordCount ) />
  • <cfset REQUEST.Pager.Calculate() />

As you can see, I am telling the paginator how many records we are dealing with (via SetRecordCount()). Then we tell the paginator to calcualte(). This takes the record count and the offset and creates all the buckets, page numbers, starting and stopping offsets, urls, and performs all validation (ie. making sure the offset is not beyond the scope of the results set). For more in depth look, see the CFC code listed above.

Once the pager is done calculating we have everything we need to perform pagination. Except of course, the data for the current page of results. This accounts for my second call to the database. It returns ALL information requires for JUST THIS SET (ie. 10 items worth of information based on 10 items per page).

  • <!--- Query for blog entries. --->
  • <cfquery name="REQUEST.EntryQuery" datasource="...">
  • SELECT
  • b.id,
  • b.name,
  • b.date_posted,
  • b.time_posted,
  • b.description,
  • b.content,
  • (
  • SELECT
  • COUNT(*)
  • FROM
  • blog_comment c
  • WHERE
  • c.blog_entry_id = b.id
  • ) AS comment_count,
  • d.is_viewable
  • FROM
  • blog_entry b
  • INNER JOIN
  • display_status d
  • ON
  • (
  • b.display_status_id = d.id
  • AND
  • (
  • 1 = 0
  •  
  • <!--- Check for IDs. --->
  • <cfif qID.RecordCount>
  •  
  • <cfloop index="intRow" from="#REQUEST.Pager.GetStart()#" to="#REQUEST.Pager.GetEnd()#" step="1">
  • OR
  • b.id = <cfqueryparam value="#qID[ 'id' ][ intRow ]#" cfsqltype="CF_SQL_INTEGER" />
  • </cfloop>
  •  
  • </cfif>
  •  
  • )
  • )
  •  
  • <!--- Check for IDs. --->
  • <cfif qID.RecordCount>
  •  
  • ORDER BY
  • (
  • CASE
  • <cfloop index="intRow" from="#REQUEST.Pager.GetStart()#" to="#REQUEST.Pager.GetEnd()#" step="1">
  • WHEN
  • b.id = <cfqueryparam value="#qID[ 'id' ][ intRow ]#" cfsqltype="CF_SQL_INTEGER" />
  • THEN
  • <cfqueryparam value="#intRow#" cfsqltype="CF_SQL_INTEGER" />
  • </cfloop>
  • ELSE
  • 0
  • END
  • ) ASC
  •  
  • </cfif>
  • </cfquery>

As you can see, this query gets a full SELECT statement. We are returning all relevant information here. However, as I stated before, we are ONLY returning information for the results we what to display. How is that done? Through the ID loop in the JOIN clause (would be in the WHERE clause if I didn't have a JOIN to work with):

  • <!--- Check for IDs. --->
  • <cfif qID.RecordCount>
  •  
  • <cfloop
  • index="intRow"
  • from="#REQUEST.Pager.GetStart()#"
  • to="#REQUEST.Pager.GetEnd()#"
  • step="1">
  •  
  • OR
  • b.id = <cfqueryparam
  • value="#qID[ 'id' ][ intRow ]#"
  • cfsqltype="CF_SQL_INTEGER"
  • />
  • </cfloop>
  •  
  • </cfif>

The CFLoop tag here is just a simple index loop that asks the pager where to START and STOP. If we are on our first page of results, then REQUEST.Pager.GetStart() and REQUEST.Pager.GetEnd() would return 1 and 10 respectively. If we were on our second page of results, it would return 11 and 20 respective (and so on). These values are determined using the Calculate() method discussed above. So, just to recap, this CFLoop determines which records are going to be returned by only selecting the records whose IDs match the subset of IDs returned in the original ID query.

Determining what records to return is one thing, sorting them is another. Remember that the original ID query returned the IDs in proper sort order. We are using that information to order the second query:

  • <!--- Check for IDs. --->
  • <cfif qID.RecordCount>
  •  
  • ORDER BY
  • (
  • CASE
  • <cfloop
  • index="intRow"
  • from="#REQUEST.Pager.GetStart()#"
  • to="#REQUEST.Pager.GetEnd()#"
  • step="1">
  •  
  • WHEN
  • b.id = <cfqueryparam
  • value="#qID[ 'id' ][ intRow ]#"
  • cfsqltype="CF_SQL_INTEGER"
  • />
  • THEN
  • <cfqueryparam
  • value="#intRow#"
  • cfsqltype="CF_SQL_INTEGER"
  • />
  • </cfloop>
  • ELSE
  • 0
  • END
  • ) ASC
  •  
  • </cfif>

This CFLoop creates a CASE statement in which records are ordered based on where their IDs fall in the order of the original query. If the current ID (in the query) matches the ID in the original query then it's ORDER BY value is the record number of the ID in the original query. Since record numbers are ascending, the ORDER BY will sort the records properly.

There is a lot of stuff going on there, so it might take some time to understand. Getting your head wrapped around the ORDER BY might be the hardest part. But the bottom line here is that I am returning a minimal amount of excess data (in this case lots of IDs even for a subset if IDs being display). And, I am NOT repeating any search logic. The search criteria is ONLY used in one query. The second query merely uses the IDs returned by the first. I think one of the coolest things though, just to drive it home, is that the main query (the second one that returns complete detail data) returns data ONLY FOR THE RECORDS BEING DISPLAYED.

Just really beat a dead horse, to contrast this method, if you were to use the CFOutput method using STARTROW and MAXROWS, or the CFLoop method using STARTROW and ENDROW, both of those would require returning more data from the database than what was required for display. My method, while returning excess ID data, minimizes the return of detailed information (such as large text fields).

Ok, so that is how the data is returned. Now, let me quick cover how I output my pagination code:

  • <div class="datapager">
  •  
  • Showing
  • #REQUEST.Pager.GetStart()# - #REQUEST.Pager.GetEnd()# of
  • #REQUEST.Pager.GetRecordCount()# &nbsp;
  •  
  • <span class="pageslabel">
  • Pages:
  • </span>
  •  
  • <cfif (REQUEST.Pager.GetBucketStartPage() GT 1)>
  • <a href="#REQUEST.Pager.ReplaceOffset((((REQUEST.Pager.GetBucketStartPage() - 2) * REQUEST.Pager.GetPageSize()) + 1))#">«</a>
  • </cfif>
  •  
  • <!--- Loop over visible pages --->
  • <cfloop index="intPage" from="#REQUEST.Pager.GetBucketStartPage()#" to="#REQUEST.Pager.GetBucketEndPage()#" step="1">
  • <a href="#REQUEST.Pager.ReplaceOffset((((intPage - 1) * REQUEST.Pager.GetPageSize()) + 1))#" class="<cfif (intPage EQ REQUEST.Pager.GetPage())>on<cfelse>off</cfif>">#intPage#</a>
  • </cfloop>
  •  
  • <cfif (REQUEST.Pager.GetBucketEndPage() LT REQUEST.Pager.GetPages())>
  • <a href="#REQUEST.Pager.ReplaceOffset(((REQUEST.Pager.GetBucketEndPage() * REQUEST.Pager.GetPageSize()) + 1))#">»</a>
  • </cfif>
  •  
  •  
  • <
  • <cfif REQUEST.Pager.GetPrev()>
  • <a href="#REQUEST.Pager.ReplaceOffset(REQUEST.Pager.GetPrev())#" class="prevnext">Prev</a>
  • <cfelse>
  • <span class="prevnext">Prev</span>
  • </cfif>
  • |
  • <cfif REQUEST.Pager.GetNext()>
  • <a href="#REQUEST.Pager.ReplaceOffset(REQUEST.Pager.GetNext())#" class="prevnext">Next</a>
  • <cfelse>
  • <span class="prevnext">Next</span>
  • </cfif>
  • >
  •  
  • </div>

This code is a bit crammed and hard to read, but if you pick through it, it is using the pagination object to loop over the pages / buckets to display page links and previous / next links. The key to it all is the ReplaceOffset() method called on the paginator:

  • href="#REQUEST.Pager.ReplaceOffset( REQUEST.Pager.GetNext() )#"

This is just one instance of the use, but it is a good example. The GetNext() method returns the offset of the next Page of results (would return 11 if we were on page one, showing results 1 - 10). The ReplaceOffset() method then takes the passed in offset and returns the dynamic URL based on the given offset:

/blog-entries.htm?search_offset=11

How easy is that? I most of my pagination display stuff in custom tag widgets so you deal with it once and then it is done. I know that this was a lot to take in. Hopefully I can put a demo of it up in the Snippets directory shortly.

Now, this is NOT the best method out there I am sure, but it is the best method that I feel comfortable with.




Reader Comments

Dan,

That looks very impressive. Unfortunately, it's a bit beyond me at this point. I have never used a stored procedure. I tried to read through it, but I am getting a bit lost. How do you return the number of overall records (other than the current page of records ... ie. 100 of 1-10). Is that a feature of stored procedures (I know you can return more than one set of data) or are you returning it as an additional query column?

Thanks for the link though, excellent food for thought.

Reply to this Comment

@Ben:

The first 8 parameters are input parameters--values that are being passed in to the stored procedure.

The last 4 paramters are output parameters--which are values that will be returned from the stored procedure. It's the output parameters that contain information such as: total number of records, total number of pages, the current page (while this is usually the same value as the SetPage input parameter, it can be differently--like if you specified a SetPage value outside of the actual page boundaries.)

I recommend first playing around w/the stored procedure in Query Analyzer--just to get familiar with it. The usage example I give should run fine in Query Analyzer--provided you supply real table information.

The stored procedure always outputs a lot of information in the message blocks to tell you how it processed the query.

So the first step would be to create the stored procedure on the database you want to use it on. You should be able to cut and paste the code from my blog into query anaylzer and run it on your database.

Here would be an example of some CF code, I'm using the MSSQL Northwind Database as an example (which would need the stored proc in order for the code to work.)

<cfstoredproc procedure="spSelectNextN" datasource="Northwind">
<!---// pass in the following parameters //--->

<cfprocparam cfsqltype="cfsqlvarchar" type="in" value="Customers" />
<cfprocparam cfsqltype="cfsqlvarchar" type="in" value="CustomerID, CompanyName, ContactName, ContactTitle, Address, City, PostalCode, Phone, Fax" />
<cfprocparam cfsqltype="cfsqlvarchar" type="in" value="CustomerID" />
<cfprocparam cfsqltype="cfsqlinteger" type="in" value="2" />
<cfprocparam cfsqltype="cfsqlinteger" type="in" value="10" />
<cfprocparam cfsqltype="cfsqlvarchar" type="in" value="Country <> 'USA'" />
<cfprocparam cfsqltype="cfsqlvarchar" type="in" value="CompanyName ASC, ContactName ASC" />
<cfprocparam cfsqltype="cfsqlbit" type="in" value="0" />

<!---// return the following parameters //--->
<cfprocparam cfsqltype="cfsqlinteger" type="out" variable="iTotalRecords" />
<cfprocparam cfsqltype="cfsqlinteger" type="out" variable="iTotalPages" />
<cfprocparam cfsqltype="cfsqlinteger" type="out" variable="iCurrentPage" />
<cfprocparam cfsqltype="cfsqlvarchar" type="out" variable="sSqlString" />

<!---// return the following resultsets //--->
<cfprocresult name="GetCustomers" resultset="1" />
</cfstoredproc>

Here's what it all means:

1) The 1st parameter is the table name to use.
2) The 2nd parameter is the columns to retrieve.
3) The 3rd parameter is the identity column from the table
4) The 4th parameter is the page of results you'd like to display
5) The 5th parameter is the number of records each page contains
6) The 6th parameter is the WHERE clause you want to use--this can be NULL.
7) The 7th parameter is the ORDER BY clase--which can be null as well.
8) The 8th parameter tells the stored proc whether or not to use the DISTINCT keyword on the query (1=DISTINCT, 0=normal)

Now we get to the output parameters.

1) The 9th parameter would return the variable iTotalRecords which would contain the total number of records the query would return.
2) The 10th parameter would return the variable iTotalPages which would contain the total number of pages found.
3) The 11th parameter would return the variable iCurrentPage which would return the actual page number the stored procedure returned.
4) The 12th parameter would return the variable sSqlString which would contain the actual SQL query statement that was executed.

Lastly, the actual results of the query are returned in the variable GetCustomers.

It's actually much simiplier than it may look. If you have really complex SQL statements you need to run pagination on, convert them to views. Usually those complex statements are used in various spots in your application and views will give you better performance. Converting the complex SQL to a view will allow you to use this stored procedure to paginate the results.

Reply to this Comment

Ben and Dan,

The ColdFusion method and the stored proc are both great, thank you!

I will certainly end up basing my own pagination logic on either one or another (or both methods). I am still unsure on why you choose to use a CASE statement in the second query... Can't we simply copy the ORDER BY clause from the query that gives us the ID's?

Thanks again!

Reply to this Comment

Mark,

You could just repeat the ORDER BY clause from the ID query. The idea of the CASE statement, however, was to try and keep it generic. I was doing my best not to repeat any logic. I did the ordering based on DB values when I got the IDs. Since I am doing this for all pagination methods, I can more or less copy and paste the ORDER BY in the final query and it works.

Basically, its a combo of not repeating myself and making copy/paste easier.

Reply to this Comment

Ben,

I like your simple SQL Stored Proc. However, you did not include any code on how you do things on the CF side. Can you give me an exmple of how you call the proc in your CF page and display the pages, etc?

Thanks,

Matthew

Reply to this Comment

Post A Comment

You — Get Out Of My Dreams, Get Into My Comments
Live in the Now
Oops!
Comment Etiquette: Please do not post spam. Please keep the comments on-topic. Please do not post unrelated questions or large chunks of code. And, above all, please be nice to each other - we're trying to have a good conversation here.