Andy Jarrett: MySQL limit cffunction for Query and QoQ's

Loading Twitter

MySQL limit cffunction for Query and QoQ's

Query of a Query functionality was introduced in ColdFusion 5 and it gives the developer the ability to re-query a returned recordset. While it does implement the core set of SQL SELECT commands the one bit of functionality I miss is MySQL's Limit function (I miss this in ANY relational database thats not MySQL as it makes paginiation a doddle).

Limit in MySQL works as such:

view plain print about

1SELECT * FROM myTable LIMIT 0, 10

The above code will display the first 10 results from your your (table is 0 indexed)

view plain print about

1SELECT * FROM myTable LIMIT 5, 5

Starting from the 5th record this will bring back rows 6, 7, 8, 9, and 10

The idea behind the my <cffunction> is to mimic Limit with a passed in query. Of course this function can be used on any returned recordset but a lot of DB's have there own way of handling this which you might want to investigate first.

view plain print about

1<cffunction name="limit" returntype="query" description="WORKS LIKE MYSQL LIMIT(N,N)">
2    <cfargument name="inQry" type="query" hint="I am the query" />
3    <cfargument name="arg1" type="numeric" />
4    <cfargument name="arg2" type="numeric" />
5
6    <cfscript>
7        var outQry = arguments.inQry;
8        var a1 = arguments.arg1-1;
9        var a2 = arguments.arg2-1;
10
11        if(arg1 GT 1){
12            outQry.RemoveRows(JavaCast( "int", 0 ), JavaCast( "int", arg1 ));
13        }
14
15        outQry.RemoveRows(JavaCast( "int", arg2 ),JavaCast( "int", outQry.recordcount-arg2));
16
17        return outQry;
18    </cfscript>
19</cffunction>

Comments (4) | Print | Send | 2763 Views

[Add Comment] [Subscribe to Comments]

Posted By Raymond Camden @ 7/17/08 8:00 AM

*cough* cflib *cough*

Posted By Pete Freitag @ 7/17/08 9:36 AM

Handy function Andy! It is annoying that every DB handles this differently, I put together a list a while back with all the different ways to limit results on different dbs: http://www.petefreitag.com/item/59.cfm

Posted By Barney @ 7/17/08 11:41 AM

You can also use the maxrows attribute of CFQUERY on your QofQ to get the limit/top functionality for free. There's not an attribute that supports offset, I use non-offset limits more than I use offset limits, so it's quite useful.

Posted By Andy Jarrett @ 7/17/08 3:58 PM

@Raymond Camden thats a nasty cough you've got there :) I've submitted it to CFLIB ... though if the submission is dodgy, sorry :)

[Add Comment]