Object Oriented Pagination in ColdFusion
There are various ways to perform pagination in ColdFusion, but I thought I would offer a few additional thoughts to the subject, including an object oriented approach to handling the results.
Getting the data
The first step is in getting the data. There seem to be many solutions to this, but the approach I will use here seems to be a nice approach that only returns the specific records for the page (rather than returning the entire record set on each query) and is hopefully not too database specific. Suppose we have a Person table with columns personId, firstName and lastName and we want to display 30 records per page ordered by last name. In this example we will display page 3 of the data.
The following query will return exactly the records we need.
<cfset page = 3>
<cfset recordsToSkip = (page - 1) * pageSize>
<cfquery name=" personData" datasource="sales">
select top #pageSize# *
from Person
where
personId not in (
select top #recordsToSkip# personId
from Person
order by lastName
)
order by lastName
</cfquery>
Filtering the data
Suppose we also have an "isActive" flag in this table, and we want to limit the result to only active records:
<cfset page = 3>
<cfset recordsToSkip = (page - 1) * pageSize>
<cfquery name=" personData" datasource="sales">
select top #pageSize# *
from Person
where
isActive = 1
and personId not in (
select top #recordsToSkip# personId
from Person
where isActive = 1
order by lastName
)
order by lastName
</cfquery>
Notice that the subquery must order and limit the records to the same as the outer query.
Removing Duplicate Code
You could also use variables for the where and order by clause to reduce duplication in the code:
<cfset page = 3>
<cfset recordsToSkip = (page - 1) * pageSize>
<cfsavecontent variable="where">
and isActive = 1
</cfsavecontent>
<cfsavecontent variable="orderBy">
order by lastName
</cfsavecontent>
<cfquery name=" personData" datasource="sales">
select top #pageSize# *
from Person
where
1=1
#where#
and personId not in (
select top #recordsToSkip# personId
from Person
where
1=1
#where#
#orderBy#
)
#orderBy#
</cfquery>
Getting the count of total records
The above query only returns the page of data you need, but does not provide you with the total number of records in your result - which you need to know to know how many pages you have in total. So you will need to issue a second SQL command to get the record count. This is the same query, but with a count (*) instead:
select count(*) as num
from Person
where
1=1
#whereClause#
</cfquery>
<cfset recordCount = personCount.num>
Using a Gateway Component
Query such as these can be placed inside a Gateway object.
In this example we implement two functions: findPeopleByPage() and getPeopleCount(), in addition to passing a Datasource object to the init() function.
Notice that we pass in the where and orderBy as parameters to the functions.
<cfset variables.datasource = 0>
<cffunction name="init" output="false">
<cfargument name="datasource">
<cfset variables.datasource = arguments.datasource>
</cffunction>
<cffunction name="findPeopleByPage" output="false">
<cfargument name="page">
<cfargument name="pageSize">
<cfargument name="where" default="">
<cfargument name="orderBy" default="">
<cfset var personData = 0>
<cfquery
name="personData"
datasource="#variables.datasource.getName()#">
<!--- Page query here. --->
</cfquery>
<cfreturn personData>
</cffunction>
<cffunction name="getPeopleCount" output="false">
<cfargument name="where" default="">
<cfset var personCount = 0>
<cfquery
name="personCount"
datasource="#variables.datasource.getName()#">
<!--- Count query here. --->
</cfquery>
<cfreturn personCount.num>
</cffunction>
</cfcomponent>
In this case, the where clause and order by are just passed in as SQL strings, but this could perhaps be improved further by passing in data structures such as structs or lists.
Pagination calculations
We need a couple of numbers for our pagination:
<cfset page = url.page>
<!--- Part of the application configuration. --->
<cfset pageSize = 30>
<!--- Provided by the getPeopleCount() function. --->
<cfset totalRecords = getPeopleCount()>
<!--- Calculate total pages available. --->
<cfset totalPages = int(totalRecords / pageSize)>
<cfif totalPages mod pageSize gt 0>
<cfset totalPages = totalPages + 1>
</cfif>
<!--- Calculate previous page. --->
<cfset previousPage = max( page-1, 1 )>
<!--- Calculate next page. --->
<cfset nextPage = min( page+1, totalPages )>
Using a Paginator Component
Taking this further, the actual handling of the pages could be done in a Paginator component. The paginator manages the where clause, order by and page size for you.
Here, we initialise the Paginator with the gateway.
<cfset variables.peopleGateway = 0>
<cfset variables.where = "">
<cfset variables.orderBy = "">
<cfset variables.pageSize = "">
<cfset variables.page = "">
<cfset variables.totalRecords = "">
<cffunction name="init" output="false">
<cfargument name="peopleGateway">
<cfargument name="pageSize">
<cfargument name="where">
<cfargument name="orderBy">
<cfset variables.peopleGateway = arguments.peopleGateway>
<cfset variables.pageSize = arguments.pageSize>
<cfset variables.where = arguments.where>
<cfset variables.orderBy = arguments.orderBy>
</cffunction>
<cffunction name="getPage" output="false">
<cfargument name="page">
<cfset var personData =
variables.peopleGateway.findPeopleByPage(
argument.page,
variables.pageSize,
variables.where,
variables.orderBy
)>
<cfreturn personData>
</cffunction>
<cffunction name="getCount" output="false">
<cfif len(variables.totalRecords) eq 0>
<cfset variables.totalRecords =
variables.peopleGateway.getPeopleCount(
variables.pageSize,
variables.where,
variables.orderBy
)>
</cfif>
<cfreturn variables.totalRecords>
</cffunction>
<cffunction name="getNumberOfPages" output="false">
<cfset var totalRecords = getCount()>
<cfset var totalPages = int(totalRecords / variables.pageSize)>
<cfif totalPages mod variables.pageSize gt 0>
<cfset totalPages = totalPages + 1>
</cfif>
<cfreturn totalPages>
</cffunction>
<cffunction name="getNextPageNumber" output="false">
<cfset var totalPages = getNumberOfPages()>
<cfset var nextPage = min(
variables.page + 1,
totalPages )>
</cffunction>
<cffunction name="getPreviousPageNumber" output="false">
<cfset var totalPages = getNumberOfPages()>
<cfset var previousPage = max(
variables.page - 1,
1 )>
<cfreturn previousPage>
</cffunction>
</cfcomponent>
An example of using our paginator:
<cfset paginator = createObject("component","PersonPaginator")>
<!---
Initialise the paginator indicating we want
a) 30 records per page
b) only active people
c) ordered by last name then first name
--->
<cfset paginator.init(
personGateway,
30,
"and isActive=1",
"order by lastName asc, firstName asc"
)>
Set up our pagination variables.
<cfparam name="url.page" default="1">
<!--- Calculate our page variables. --->
<cfset page = url.page>
<cfset pageData = paginator.getPage(page)>
<cfset totalPages = paginator.getNumberOfPages()>
<cfset prevPage = paginator.getPreviousPageNumber()>
<cfset nextPage = paginator.getNextPageNumber()>
Display the page of data
<cfloop query="pageData">
<cfoutput>#lastName#, #firstName#<br /></cfoutput>
</cfloop>
Display the nagivator that lets you move from page to page.
<a href="page.cfm?page=#prevPage#">Previous</a>
Page #page# of #totalPages#
<a href="page.cfm?page=#nextPage#">Next</a>
Wrapping up
For extremely large record sets you may need to seek a more efficient solition, but this should be fine if you are dealing with thousands (or even a few hundred thousand) records.
This entry was inspired by a PHP thread on pagination ( http://www.phpbuilder.com/board/showpost.php?p=10668964&postcount=13 ),
with tbl as
(
select u.lastName, u.firstName
ROW_NUMBER() over (order by u.lastName, u.firstName) as RowNum
from users u
)
select * from tbl
where
RowNum between @startRow and @endRow
order by
lastName, firstName
Cheers.
Nice code. Ben Nadel and I have had similar discussions recently on pagination, and here's what I'm currently using. ;)
http://cfzen.instantspot.com/blog/index.cfm/2007/1...
I am just about to build my pagination system into a huge project I'm working on but was having real problems working out how to handle this in a OO way and integrate it with Transfer.
This post was pretty much written for me as you cover just about everything I need. I was going to create a DataTools singleton which would handle pagination but quite like your pagination cfc which I could just inject into my gateways as needed. I should be able to make it quite generic with perhaps standard API functions in each gateway..
Thanks again for this excellent post - spot on.
However, I'm having some issues with the code, I'm getting a syntax error when I try to run it.
Here is my code:
<cfloop query = "getNames">
<cfset pageSize = 30>
<cfset page = #page# + 1>
<cfset recordsToSkip = (#page# - 1) * #pageSize#>
<cfquery name="getLabour" datasource="andes">
SELECT top #pageSize# * FROM control_lab
where conID not in(
select top #recordsToSkip# conID
from control_lab
and proID = '#URL.proID#'
AND empID = '#getNames.empID#'
order by proDate desc
)
and proID = '#URL.proID#'
AND empID = '#getNames.empID#'
order by proDate desc
</cfquery>
Any idea?
Thank you very much
Dani
<cfset pageSize = 30>
<cfset page = #page# + 1>
<cfset recordsToSkip = (#page# - 1) * #pageSize#>
<cfquery name="getLabour" datasource="andes">
SELECT top #pageSize# * FROM control_lab
where conID not in(
select top #recordsToSkip# conID
from control_lab
order by proDate desc
)
and proID = '#URL.proID#'
AND empID = '#getNames.empID#'
order by proDate desc
</cfquery>
Still getting the syntax error:
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '30 * FROM control_lab where conID not in( select top 0 conID from con' at line 1
It looks like MySQL does not support the TOP syntax. From the article here - http://www.anticlue.net/archives/000321.htm - looks like you need to use the LIMIT command.
My guess would be that it goes something like this:
<cfquery name="personData" datasource="sales">
select *
from Person
where
personId not in (
select personId
from Person
LIMIT #recordsToSkip#
)
LIMIT #pageSize#
order by lastName
</cfquery>
But unfortunately I don't have a copy of MySQL to try it out.
<cfset pageSize = 30>
<cfset page = page + 1>
<cfset recordsToSkip = (page - 1) * pageSize>
<cfquery name="getLabour" datasource="andes">
select * from control_lab
where conID not in (
select conID
from control_lab
order by proDate desc
LIMIT #recordsToSkip#
)
and proID = '#URL.proID#'
and empID = '#getNames.empID#'
order by proDate desc
LIMIT #pageSize#
</cfquery>
Hopefully that is closer to a working query.
According to the error page I get now, LIMIT within a subquery is not accepted.
Maybe if I tell you what I'm trying to do you can have an extra idea.
I have a report generated with cfdocument that lists the daily labour activity in a particular project by employee (for a construction company). All the employees that were working on the project are listed in this report in their on page (i'm doing a page break at the end of the employees's loop).
With small projects works ok because the activity for each employee fits in one page. But for large projects, there will be a record of information that will be cut off when the cfoutput tag reaches the bottom of the page.
That's why i need to determine the max rows to print per page per employee.
Thanks again!!
dani
Looks like this may be simpler in MySQL than in MSSQL2000; we don't need the subquery. Looking up the LIMIT syntax - http://dev.mysql.com/doc/refman/5.0/en/select.html... - looks like we get something like:
<cfset pageSize = 30>
<cfset page = page + 1>
<cfset recordsToSkip = (page - 1) * pageSize>
<cfquery name="getLabour" datasource="andes">
select *
from control_lab
where proID = '#URL.proID#'
and empID = '#getNames.empID#'
order by proDate desc
limit #recordsToSkip#, #pageSize#
</cfquery>
I haven't tested this but, who knows, it might just work.