SELECT TOP And ColdFusion Query Of Queries
Posted November 28, 2006 at 5:00 PM
NOTE: Please disregard the following post. It is solved using a CFQuery Attribute, MAXROWS, that I didn't know about. (Thanks Joe!)... you think you know a language and them BLAM! You don't.
Using the SELECT TOP directive of a SQL statement is a nice way to limit the number of records that are returned from the database. Unfortunately (as far as I can see), ColdFusion Query of Queries (QoQ) do NOT support the SELECT TOP directive. There is, however, a fairly easy way to mimic this using the Java methods of the ColdFusion query record set. This is not officially supported:
Launch code in new window » Download code as text file »
- <!--- Create movie query. --->
- <cfset qMovie = QueryNew(
- "name",
- "CF_SQL_VARCHAR"
- ) />
-
- <!--- Add movie rows. --->
- <cfset QueryAddRow( qMovie, 10 ) />
-
- <!--- Set query data. --->
- <cfset qMovie[ "name" ][ 1 ] = "Terminator" />
- <cfset qMovie[ "name" ][ 2 ] = "Terminator 2" />
- <cfset qMovie[ "name" ][ 3 ] = "Terminator 3" />
- <cfset qMovie[ "name" ][ 4 ] = "Predator" />
- <cfset qMovie[ "name" ][ 5 ] = "Twins" />
- <cfset qMovie[ "name" ][ 6 ] = "Read Head" />
- <cfset qMovie[ "name" ][ 7 ] = "True Lies" />
- <cfset qMovie[ "name" ][ 8 ] = "Eraser" />
- <cfset qMovie[ "name" ][ 9 ] = "Kindergarten Cop" />
- <cfset qMovie[ "name" ][ 10 ] = "Pumping Iron" />
-
- <!--- Select the movies. --->
- <cfquery name="qSubMovie" dbtype="query">
- SELECT
- name
- FROM
- qMovie
- ORDER BY
- name ASC
- </cfquery>
-
-
- <!---
- Get the TOP 3 movies. We only need to do this if the
- record set is GREATER than 3 records long.
- --->
- <cfif (qSubMovie.RecordCount GT 3)>
-
- <!---
- Remove all rows beyond 3 (index 3 and
- greater from the Java stand point).
- --->
- <cfset qSubMovie.RemoveRows(
- JavaCast( "int", 3 ),
- JavaCast( "int", qSubMovie.RecordCount - 3 )
- ) />
-
- </cfif>
If you are not comfortable with this, you could manually build the second query and then add the rows, but that is pain. And, to get that to work, you have to run a Query of Queries anyway in order to get any WHERE or ORDER criteria to work. At that point, why go through the hassle of doing any more manual work than in necessary.
If you do go this way, you might want to hide the implementation into a user defined function such as QueryTrim():
Launch code in new window » Download code as text file »
- <cffunction
- name="QueryTrim"
- access="public"
- returntype="query"
- output="false"
- hint="Trims a query to the number of rows requested.">
-
- <!--- Define arguments. --->
- <cfargument name="Query" type="query" required="true" />
- <cfargument name="RecordCount" type="numeric" required="true" />
-
- <!--- Check to see if we have rows to remove. --->
- <cfif (ARGUMENTS.Query.RecordCount GT ARGUMENTS.RecordCount)>
-
- <!--- Trim the recordset. --->
- <cfset ARGUMENTS.Query.RemoveRows(
- JavaCast( "int", ARGUMENTS.RecordCount ),
- JavaCast( "int", (ARGUMENTS.Query.RecordCount - ARGUMENTS.RecordCount) )
- ) />
-
- </cfif>
-
- <!---
- Return the updated query. This is NOT required as
- the query object is passed by reference, not by value.
- However, this would be useful to do as it allows for
- method chaining.
- --->
- <cfreturn ARGUMENTS.Query />
- </cffunction>
You would then call this as follows:
Launch code in new window » Download code as text file »
- <!--- Trim query. --->
- <cfset QueryTrim(
- qSubMovie,
- 3
- ) />
Not only is this perhaps easier to read and less work, it allows you to change the implementation of the way the SELECT TOP mimicry works. Personally, I think it would be great for them to just build SELECT TOP into the ColdFusion query of queries (how hard could it be?). But, until then, this has worked very nicely for me.
Download Code Snippet ZIP File
Comments (7) | Post Comment | Ask Ben | Permalink | Other Searches | Print Page
ColdFusion Regular Expressions Match New Line Using "."
Peter Bell Is Killing My Productivity (In The Short Term)
Reader Comments
Hey Ben,
Is there a reason why you'd do this and not use MAXROWS on the QoQ, such as:
# <cfquery name="qSubMovie" dbtype="query" maxrows="10">
# SELECT
# name
# FROM
# qMovie
# ORDER BY
# name ASC
# </cfquery>
-Joe
Posted by Joe Rinehart on Nov 28, 2006 at 6:01 PM
Yeah, cause I don't think I even knew there was a MAXROWS attribute ;)
Thanks Joe! Man, I wish knew all the ins an outs of the tags.
Posted by Ben Nadel on Nov 28, 2006 at 6:03 PM
Heheh...no worries! I can't tell you how long I used replaceNoCase(someList, ",", "','", "all") before I realized ListQualify() existed.
My rule of thumb for CF: if there's a common task that you'd like to implement code to accomplish, it's probably in the library.
-Joe
Posted by Joe Rinehart on Nov 28, 2006 at 9:28 PM
Yeah, ColdFusion is so freakin' cool. I just never needed to use MAXROWS before (that I can remember) for standard queries as I could always just use TOP. This gives me something nice to bring up in next week's Staff Meeting (yeah, I like to share the knowledge).
On an unrelated topic, I am reading your article on Model-Glue Fundamentals in the Fushion Authority Quarterly. Very nice. So was the article on Mach-ii by Matt Woodward. The both of you have made these frameworks much clearer. I have looked at them before online, but for some reason never could bridge some knowledge gap. These articles, somehow, got me there. I get it now :) Not that I understand it all, but some things finally clicked. Maybe its the pressure of having to have it ALL in one small article or something... I don't know what it is, but the examples and explanations are just really nice. So, thanks.
Posted by Ben Nadel on Nov 29, 2006 at 7:30 AM
Ben:
Although it might be unnecessary for what you had in mind, this worked perfectly for something I was working on today! I am taking several similar queries and joining them using a ColdFusion query of queries UNION. But since I cannot do a TOP in the SELECT statement, I used the querytrim() function to remove excess rows from the query before running it through the QoQ.
Thanks!!
Posted by Mark Mazelin on Jan 24, 2007 at 2:33 PM
Mark, good stuff!
Posted by Ben Nadel on Jan 24, 2007 at 2:55 PM
Thank you for the post - and the answer! I was encountering the exact same issue, and didn't even think to look at maxrows!
Danke, dahling!
Best wishes,
Cat
Posted by Cat on May 5, 2008 at 11:11 AM
