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 | 2824 Views

If you like what you see on the website and/or this post has helped you out in some way please consider donating to help keep me in beer vodka. The donations are made through Paypal, which accepts almost any credit card or eCheck.

(Comment Moderation is enabled. Your comment will not appear until approved.)

[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]