Coldfusion Pagable Query
By jonkarna
I wrote this coldfusion class to encapsulate much of the logic that goes along with creating paginated results. I grew tired of the duplicate code that went from project to project.
Using PageableQuery.cfc
To initialize your object, pass in the original query with the desired page size. To retreive the information related to a specific page, pageableQuery.getPage(page) will retrieve a limited query focused only on the records for the specific page.
PageableQuery.cfc
<cfcomponent>
<cffunction name="init" access="public" output="false" returntype="any">
<cfargument name="records" required="true" type="query" />
<cfargument name="pageSize" required="true" type="numeric" />
<cfset variables.records = arguments.records />
<cfset variables.pageSize = arguments.pageSize />
<cfreturn this />
</cffunction>
<cffunction name="getQuery" access="private" output="false" returntype="query">
<cfreturn variables.records />
</cffunction>
<cffunction name="getPageSize" access="private" output="false" returntype="numeric">
<cfreturn variables.pageSize />
</cffunction>
<cffunction name="getPageOffset" access="private" output="false" returntype="numeric">
<cfargument name="page" required="true" type="numeric" />
<cfreturn (arguments.page - 1) * getPageSize() />
</cffunction>
<!--- public --->
<cffunction name="getAbsoluteObject" access="public" output="false" returntype="query">
<cfargument name="absolutePosition" required="true" type="numeric" />
<cfset var i = 1 />
<cfset var qryRecord = QueryNew(getQuery().columnList) />
<cfset QueryAddRow(qryRecord, 1) />
<cfloop index="i" from="1" to="#ListLen(getQuery().columnList)#">
<cfset QuerySetCell(qryRecord, ListGetAt(getQuery().columnList, i), variables.records[ListGetAt(getQuery().columnList, i)][arguments.absolutePosition]) />
</cfloop>
<cfreturn qryRecord />
</cffunction>
<cffunction name="getAbsolutePosition" access="public" output="false" returntype="numeric">
<cfargument name="record" required="true" type="numeric" />
<cfargument name="page" required="true" type="numeric" />
<cfset var position = getPageOffset(arguments.page) + arguments.record />
<cfif position gt getQuery().recordCount>
<cfset position = getQuery().recordCount />
</cfif>
<cfreturn position />
</cffunction>
<cffunction name="getPage" access="public" output="false" returntype="query">
<cfargument name="page" required="true" type="numeric" />
<cfset var i = 1 />
<cfset var recordCount = getPageSize() />
<cfset var qryPage = QueryNew(getQuery().columnList) />
<cfif getAbsolutePosition(recordCount, arguments.page) gte getQuery().recordCount>
<cfset recordCount = getQuery().recordCount mod getPageSize() />
<cfif recordCount eq 0>
<cfset recordCount = getPageSize() />
</cfif>
</cfif>
<cfset QueryAddRow(qryPage, recordCount) />
<cfloop index="i" from="1" to="#recordCount#">
<cfloop index="j" from="1" to="#ListLen(getQuery().columnList)#">
<cfset qryPage[ListGetAt(getQuery().columnList, j)][i] = variables.records[ListGetAt(getQuery().columnList, j)][getPageOffset(arguments.page) + i] />
</cfloop>
</cfloop>
<cfreturn qryPage />
</cffunction>
<cffunction name="getPages" access="public" output="false" returntype="numeric">
<cfreturn Ceiling(getQuery().recordCount / pageSize) />
</cffunction>
<cffunction name="getRecordPage" access="public" output="false" returntype="numeric">
<cfargument name="absolutePosition" required="true" type="numeric" />
<cfreturn Ceiling(arguments.absolutePosition / pageSize) />
</cffunction>
<cffunction name="getRecordsAvailable" access="public" output="false" returntype="numeric">
<cfreturn getQuery().recordCount />
</cffunction>
</cfcomponent>
Comments
No comments yet.
