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 pageSize = 30> <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 pageSize = 30> <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 pageSize = 30> <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:
<cfquery name="personCount" datasource="sales"> 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.
<cfcomponent output="false"> <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:
<!--- Provided by the application, such as via a URL parameter. ---> <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.
<cfcomponent name="PersonPaginator" output="false"> <cfset variables.personGateway = 0> <cfset variables.where = ""> <cfset variables.orderBy = ""> <cfset variables.pageSize = ""> <cfset variables.page = 0> <cfset variables.totalRecords = ""> <cffunction name="init" output="false"> <cfargument name="personGateway"> <cfargument name="pageSize"> <cfargument name="where"> <cfargument name="orderBy"> <cfset variables.personGateway = arguments.personGateway> <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 = 0> <cfset variables.page = arguments.page> <cfset personData = variables.personGateway.findPeopleByPage( variables.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.personGateway.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 totalRecords 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 )> <cfreturn nextPage> </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:
<!--- Create our paginator component. ---> <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.
<!--- Let's assume the page is pased in via the URL. ---> <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
<!--- Display the data ---> <cfloop query="pageData"> <cfoutput>#lastName#, #firstName#<br /></cfoutput> </cfloop>
Display the nagivator that lets you move from page to page.
<cfoutput> <!--- Display the page navigator ---> <a href="page.cfm?page=#prevPage#">Previous</a> Page #page# of #totalPages# <a href="page.cfm?page=#nextPage#">Next</a> </cfoutput>
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
),
22 Comments
With Ms-SQL 2005 / Oracle, you can use ROW_NUMBER() for much better performance:
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.
Hi Rob, thanks for the tip!
@Kevan,
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/10/11/Paginating-Records-in-CF-with-One-SQL-Server-2005-call
Hi Aaron, thanks for the link.
Update: Fixed an error in the code that returned the count of people.
What a truly wonderful post Kevan!!
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.
Thanks James!
Hello Kevan, thanks for the tutorial.
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
Correction to my previous code:
<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
Hi Dani
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.
Oops, I forgot the ORDER BY in the subquery, and let’s use your code instead:
<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.
Hello Kevan, thanks so much for taking the time to check and edit the code.
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
Hi 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.
Hi! Thanks for your post, it has been very useful. I want to share some bugs that I have found:
1. I really don’t understand how do you use/call the "gateway", what I did is to extend from the "gateway" in the peoplePaginator component
?????? no idea how to use this: <cfset variables.peopleGateway = arguments.peopleGateway>
My solution: <cfcomponent extends="peoplegateway" output="false">
2. You named the component "PeoplePaginator" but you are calling it as a "PersonPaginator". That is ambigous and it doesn’t work (at least for me)
3. In getNumberOfPages function, when you are doing the "mod" calc, you are using the wrong var
WRONG: <cfif totalPages mod variables.pageSize gt 0>
GOOD: <cfif totalRecords mod variables.pageSize gt 0>
4. On PeoplePaginator init, I think you need to pass the current page number as an argument
+++ when using the object +++
<cfparam name="url.page" default="1">
<cfset page = url.page>
<cfset paginator.init(
10,
"and isActive=1",
"order by lastName asc, firstName asc",
page <<<—- This is the new parameter
)>
+++++
=== init function within the component ===
<cffunction name="init" output="false">
<cfargument name="pageSize">
<cfargument name="where">
<cfargument name="orderBy">
<cfargument name="currentPageNumber"> <<<—- This is the new argument
<cfset variables.pageSize = arguments.pageSize>
<cfset variables.where = arguments.where>
<cfset variables.orderBy = arguments.orderBy>
<cfset variables.page = arguments.currentPageNumber> <<<—- This is the new argument
</cffunction>
======
5. This function is not returning a value getNextPageNumber
6. Here you have to put the output coldfusion tags:
<cfoutput><a href="test.cfm?page=#prevPage#">Previous</a>
Page #page# of #totalPages#
<a href="test.cfm?page=#nextPage#">Next</a></cfoutput>
I think there are others errors but I dind’t track them. If you want to download the code (working) you can do it from: http://dev.dualcredit.itap.purdue.edu/mescalito2-paginator.rar
and by the way, remember to change the datasources
Thanks!
Mescalito2, thanks for such a thorough review and corrections. Regarding your points:
1. The gateway would be used something like this:
<!— First create the gateway object separately —>
<cfset personGateway = createObject("component","PersonGateway").init(dsn)>
<!— Then create the pagination and provide the gateway as a parameter —>
<cfset paginator = createObject("component","PersonPaginator").init(
personGateway,
30,
"and isActive=1",
"order by lastName asc, firstName asc"
)>
This type of relationship is called composition (the gateway is "composed" inside the paginator). Composition is a generally preferred relationship over inheritance (where the paginator extends the gateway).
Inside the paginator, whenever you need to use the gateway you would refer to variables.personGateway
2. It should be named PersonPaginator. Thanks, I have fixed this.
3. Thanks, fixed.
4. I can see what you are getting at. What was missing was the page should have been set inside the getPage() function and the page should have been initialised to zero. Not sure about setting the current page on init() and then /also/ passing it in to the getPage() function. I think I will leave it out of the init() for now.
5. Thanks, fixed.
6. Yes, would be nicer to include the cfoutput’s
Thanks for letting me know about this swarm of typos!
hi,
really i don’t know how to use the paginator example ,can you explain what are content come under .cfc pages and .cfm pages
Hi Thangapandyan, sorry not sure what you are asking. All of the cfc and cfm code is in the article.
Hi,
How can i configure the gateway in coldfusion administrator.
Hi Thangapandyan, this is not an event gateway that you configure in coldfusion administrator. This cfc is named a gateway keeping in line with the gateway design pattern (http://martinfowler.com/eaaCatalog/gateway.html). It’s just a regular cfc.
Hi,
i have two coldfusion component files using for to arrive pagination in my application,file name called personGateway.cfc and personpaginator.cfc,
first file name contains below details,
select top #pageSize# *
from mstproperty
where
1=1
#where#
and pid not in (
select top #recordsToSkip# pid
from mstproperty
where
1=1
#where#
#orderBy#
)
#orderBy#
select count(*) as num
from mstproperty
where
1=1
#where#
and second file contains below details,
and i a am using the below file for calling the coldfusion component file to my application for arrive the pagination in my application,file name called pagination1.cfm
and marketid = 3 and propertytypeid = 2 and statusid not in (1,7)
order by propertyname
#lastName#, #firstName#
Previous
Page #page# of #totalPages#
Next
but when i ran the above code i got the below error
The web site you are accessing has experienced an unexpected error.
Please contact the website administrator.
The following information is meant for the website developer for debugging purposes.
Error Occurred While Processing Request
Local variable personData on line 25 must be grouped at the top of the function body.
The error occurred in D:\web\Oconnordata\Pagination1.cfm: line 16
14 :
15 :
16 :
17 :
18 :
——————————————————————————–
Resources:
Check the ColdFusion documentation to verify that you are using the correct syntax.
Search the Knowledge Base to find a solution to your problem.
Browser Mozilla/4.0 (compatible; MSIE 7.0; Windows NT 5.1; Trident/4.0; GTB6.6; .NET CLR 2.0.50727; .NET CLR 3.0.4506.2152; InfoPath.2; .NET CLR 3.5.30729; .NET4.0C; .NET4.0E; MSN OptimizedIE8;ENIN)
Remote Address 127.0.0.1
Referrer
Date/Time 12-Sep-11 05:00 PM
Stack Trace (click to expand)
at cfPagination12ecfm889454527.runPage(D:\web\Oconnordata\Pagination1.cfm:16)
coldfusion.compiler.ASTvariableDefinition$VarPositionException: Local variable personData on line 25 must be grouped at the top of the function body.
at coldfusion.compiler.ASTvariableDefinition.checkCfscript(ASTvariableDefinition.java:158)
at coldfusion.compiler.ASTvariableDefinition.checkPosition(ASTvariableDefinition.java:116)
at coldfusion.compiler.ASTvariableDefinition.register(ASTvariableDefinition.java:87)
at coldfusion.compiler.SemanticAnalyzer.transform(SemanticAnalyzer.java:196)
at coldfusion.compiler.Treewalker.postorder(Treewalker.java:86)
at coldfusion.compiler.Treewalker.postorder(Treewalker.java:27)
at coldfusion.compiler.Treewalker.postorder(Treewalker.java:27)
at coldfusion.compiler.Treewalker.postorder(Treewalker.java:27)
at coldfusion.compiler.NeoTranslator.translateJava(NeoTranslator.java:307)
at coldfusion.compiler.NeoTranslator.translateJava(NeoTranslator.java:119)
at coldfusion.runtime.TemplateClassLoader$1.fetch(TemplateClassLoader.java:310)
at coldfusion.util.LruCache.get(LruCache.java:180)
at coldfusion.runtime.TemplateClassLoader$TemplateCache.fetchSerial(TemplateClassLoader.java:254)
at coldfusion.util.AbstractCache.fetch(AbstractCache.java:58)
at coldfusion.util.SoftCache.get(SoftCache.java:81)
at coldfusion.runtime.TemplateClassLoader.findClass(TemplateClassLoader.java:476)
at coldfusion.runtime.RuntimeServiceImpl.getFile(RuntimeServiceImpl.java:752)
at coldfusion.runtime.RuntimeServiceImpl.resolveTemplatePath(RuntimeServiceImpl.java:733)
at coldfusion.runtime.TemplateProxyFactory.getTemplateFileHelper(TemplateProxyFactory.java:1377)
at coldfusion.runtime.TemplateProxyFactory.resolveName(TemplateProxyFactory.java:1330)
at coldfusion.cfc.ComponentProxyFactory.getProxy(ComponentProxyFactory.java:38)
at coldfusion.runtime.ProxyFactory.getProxy(ProxyFactory.java:65)
at coldfusion.runtime.CFPage.createObjectProxy(CFPage.java:4638)
at coldfusion.runtime.CFPage.CreateObject(CFPage.java:4483)
at cfPagination12ecfm889454527.runPage(D:\web\Oconnordata\Pagination1.cfm:16)
at coldfusion.runtime.CfJspPage.invoke(CfJspPage.java:196)
at coldfusion.tagext.lang.IncludeTag.doStartTag(IncludeTag.java:483)
at coldfusion.filter.CfincludeFilter.invoke(CfincludeFilter.java:65)
at coldfusion.filter.ApplicationFilter.invoke(ApplicationFilter.java:288)
at coldfusion.filter.RequestMonitorFilter.invoke(RequestMonitorFilter.java:48)
at coldfusion.filter.MonitoringFilter.invoke(MonitoringFilter.java:40)
at coldfusion.filter.PathFilter.invoke(PathFilter.java:86)
at coldfusion.filter.LicenseFilter.invoke(LicenseFilter.java:27)
at coldfusion.filter.ExceptionFilter.invoke(ExceptionFilter.java:70)
at coldfusion.filter.ClientScopePersistenceFilter.invoke(ClientScopePersistenceFilter.java:28)
at coldfusion.filter.BrowserFilter.invoke(BrowserFilter.java:38)
at coldfusion.filter.NoCacheFilter.invoke(NoCacheFilter.java:46)
at coldfusion.filter.GlobalsFilter.invoke(GlobalsFilter.java:38)
at coldfusion.filter.DatasourceFilter.invoke(DatasourceFilter.java:22)
at coldfusion.CfmServlet.service(CfmServlet.java:198)
at coldfusion.bootstrap.BootstrapServlet.service(BootstrapServlet.java:89)
at jrun.servlet.FilterChain.doFilter(FilterChain.java:86)
at coldfusion.monitor.event.MonitoringServletFilter.doFilter(MonitoringServletFilter.java:42)
at coldfusion.bootstrap.BootstrapFilter.doFilter(BootstrapFilter.java:46)
at jrun.servlet.FilterChain.doFilter(FilterChain.java:94)
at jrun.servlet.FilterChain.service(FilterChain.java:101)
at jrun.servlet.ServletInvoker.invoke(ServletInvoker.java:106)
at jrun.servlet.JRunInvokerChain.invokeNext(JRunInvokerChain.java:42)
at jrun.servlet.JRunRequestDispatcher.invoke(JRunRequestDispatcher.java:286)
at jrun.servlet.ServletEngineService.dispatch(ServletEngineService.java:543)
at jrun.servlet.jrpp.JRunProxyService.invokeRunnable(JRunProxyService.java:203)
at jrunx.scheduler.ThreadPool$DownstreamMetrics.invokeRunnable(ThreadPool.java:320)
at jrunx.scheduler.ThreadPool$ThreadThrottle.invokeRunnable(ThreadPool.java:428)
at jrunx.scheduler.ThreadPool$UpstreamMetrics.invokeRunnable(ThreadPool.java:266)
at jrunx.scheduler.WorkerThread.run(WorkerThread.java:66)
kindly help us to resolve this.
Thanks in advance.
Hi Thangapandyan
It looks like there is a typo in the getPage function. I’ve fixed it in the post. Rather than this:
<cfset variables.page = arguments.page>
<cfset var personData = ...>
it should have read:
<cfset var personData = 0>
<cfset variables.page = arguments.page>
<cfset personData = ...>