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

<!---
	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]"
	) />

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

<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>

<!--- 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>

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

<!--- 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>

<!--- 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>

<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>

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