Replicating MySQL's LIMIT clause in ColdFusion

I'm working on a project at the moment where the database engine hasn't been decided on, but I need to be getting on with building the core components. One of the features I need is paging. Various databases handle this in different ways and some don't do it at all. For example, MySQL 4+ has the LIMIT clause and MS SQL Server 2005+ has ROWNUMBER().

In the end I decided to try and do it in code rather than at the database level, which isn't as effecient but is database agnostic. As we all know ColdFusion is built on Java and this means we can take advantage of the underlying methods, one of which is removerows. 

RemoveRows takes to arguments; the start index (zero based!) and the number of rows to remove.

So here is my function:

<cffunction name="limit" 
  output="false" 
  hint="I limit the size of a ColdFusion Query">
  <cfargument name="recordset" 
    type="query"
    required="true" 
    hint="The query to limit in size" />
  <cfargument name="page" 
    required="false" 
    default="1" 
    type="numeric" 
    hint="The page number in the recordset" />
  <cfargument name="pagesize" 
    required="false" 
    default="10" 
    type="numeric" 
    hint="The number of rows per page" />
  
  <cfscript>
  var end = arguments.page * arguments.pagesize;
  
  // remove last n rows
  recordset.removeRows( end, recordset.recordcount - end );
  
  if ( arguments.page gt 1)
  {
    // remove first n rows
    recordset.removeRows( 0, end - arguments.pagesize );
  }
  
  return recordset;
  </cfscript>
  
</cffunction>

And here's some code to prove it works!

<cfset qList = QueryNew("id")>

<cfloop from="1" to="200" index="index">
  <cfset QueryAddRow( qList ) />
  <cfset QuerySetCell( qList, "id", index ) />
</cfloop>

<cfdump var="#qList.RecordCount#">
<cfdump var="#limit( qList, 2 )#">

• Posted in:
• SQL
• ColdFusion

8 comments

1. Why not just use the "maxrows" attribute of cfquery if you are doing in in CF anyway? You could even do that from query of queries.
   
   Alternately, you could use DataMgr and have the work done at the database level but still be database agnostic.
   www.bryantwebconsulting.com/docs/datamgr/get-records.cfm
   
   Comment by Steve Bryant – June 08, 2009
2. If you still want to do it DB agnostic, but utilize SQL, you can do it with pure SQL.
   
   While this is an MSSQL stored procedure, you could easily convert this to CF code (as it just produces a dynamic SQL statement:)
   
   blog.pengoworks.com/index.cfm/2006/6/19/MSSQL-2k-Stored-Procedure-for-Pagination
   
   While this isn't as efficient as using native commands, it works and is much more efficient that doing it in CF--especially with large recordsets.
   
   If you're only dealing with a few hundred records, then pulling down all the data to paginate may not hurt your application's performance, but as your dataset grows this quickly becomes and extremely inefficient way to handle things.
   
   Comment by Dan G. Switzer, II – June 08, 2009
3. Can't use maxrows in a QoQ because that will only give you the first n rows. What John is trying to do is return only rows x through y (e.g. 10-20) instead of just the first x rows.
   
   There's a nice CFC out there that we use called pagination.cfc that's quite nice to work with: paginationcfc.riaforge.org/
   
   Comment by James – June 08, 2009
4. Hi everyone, thanks for the comments. I guess I should explain a bit more about why I looked at doing it this way.
   
   The project I'm working on is using Transfer ORM, which returns a query object for the list method. When I was displaying the list, I needed the Transfer Object's with their rich business logic. However as CF is quite slow at creating objects I only wanted to get the ones I was showing. I could've just done a getTransfer().getXYZ() inside a cfloop in the view but that's a bit messy. Building a new Query object for records x to y is just plain slow and it is nice to iterate through an array of objects (when you are working with objects) so this seemed like a good compromise.
   
   Steve - I'm a big fan of DataMgr as you know and use it on several projects. Transfer fits the bill for this project as it will move to hibernate when CF supports it.
   
   Dan - I had a look at you SP. Nice work! Totally agree that the work should ideally be done in the database for performance, but the choice of frameworks meant that I needed to do a CF solution. Sadly I don't have time to write my own ORM!
   
   James - yeah, you're right, I wanted a specific block of rows. I have looked at the paginationcfc in the past but had issues with it and SES urls, although this wouldn't be an issue for waht I'm doing here.
   
   Once again thanks for the comments. :)
   
   Comment by John Whish – June 08, 2009
5. Also playing around with different ways of doing things is always fun!
   
   Comment by John Whish – June 08, 2009
6. John,
   
   I missed that it was rows x to y. I definitely wouldn't criticize anyone for using Transfer over DataMgr where it is a better fit.
   
   Dan,
   
   That stored proc looks nice. I will have to give it a good look when I have the time to wrap my head around the whole thing.
   
   Comment by Steve Bryant – June 08, 2009
7. Using underlying Java methods is an eloquent way to meet your project requirements. Nice job.
   The only thing you have to worry about now is if the Java implementation changes when you update Java in the future.
   Also, if I were you I'd provide some note within that function that mentions the fact that these methods are Java methods and not CF methods. Reminders are nice to have.
   
   Comment by Russ – June 09, 2009
8. Russ, that's a good point that you make about the possibility of the implementation changing. It is also a string case for writing test (TDD or BDD) as provided that I run my tests on a dev environment with an updated JDK then any issues would be flagged. It also means I can run a suite of tests against different engines such as Railo, Open Blue Dragon etc to find any issues.
   
   Comment by John Whish – June 09, 2009