Published:
Warning: This blog entry was written two or more years ago. Therefore, it may contain broken links, out-dated or misleading content, or information that is just plain wrong. Please read on with caution.
Step 1: Calculate StartRow and EndRow
This is a simple calculation but something we will want to reuse so I wrapped it up in its own function. The math is pretty self explanatory.
Note: This calculation is for SQLServer where we will start row numbering at one. If you use MySQL you would start numbering at zero.
<cffunction name="calcPageRows" access="public" output="false" returntype="struct">
<cfargument name="page" type="numeric" required="true">
<cfargument name="pagesize" type="numeric" required="true">
<cfset var rowStruct = structNew()>
<cfset rowStruct.startRow = (arguments.page * arguments.pageSize)-(arguments.pageSize - 1)>
<cfset rowStruct.endRow = (arguments.page * arguments.pageSize)>
<cfreturn rowStruct />
</cffunction>
Step 2 & 3: Get The Data From The Database Plus Total Row Count
Now that we know the startrow and endrow we can query the database for the records based on the ordering, any optional filtering and return just the desired range.
For simplicities sake I am going to swap in the stored procedure from my previous blog entry on Stored Proc Paging. This stored proc returns both the desired records plus the total number of matching records, but you could just as easily use two cfquery's to accomplish the same goal.
<cfstoredproc procedure="dbo.getUsers_paged">
<cfprocparam cfsqltype="cf_sql_varchar" value="#arguments.sortCol#">
<cfprocparam cfsqltype="cf_sql_varchar" value="#arguments.sortDir#">
<cfprocparam cfsqltype="cf_sql_integer" value="#arguments.startRow#">
<cfprocparam cfsqltype="cf_sql_integer" value="#arguments.endRow#">
<cfprocresult name="returnQuery">
</cfstoredproc>
Step 4: Format The Return Data For CFGrid
The last operation we need to perform is presenting the data to the cfgrid in a structure it can recognize. I use the term structure intentionally because that is exactly what it expects.
The queryConvertForGrid() function returns a struct with two keys TotalRowCount and query. Its pretty obvious that TotalRowCount key is just the total number of records that exist in the database for the filter criteria and the query is the subset of those records needed for the current page number.
So all we need to do is replicate this structure.
<cffunction name="queryToCFGrid" access="public" output="false" returntype="struct">
<cfargument name="theQuery" required="true" type="query">
<!---
This function operates on the basis that
the query contains a column [totalRows] where
the total number of matching records is noted
--->
<cfset var returnStruct = structNew()>
<cfset returnStruct.totalRowCount = theQuery.totalRows>
<cfset returnStruct.query = theQuery>
<cfreturn returnStruct />
</cffunction>
Reader Comments