Kevin Hoyt commented on my blog post yesterday about ColdFusion Makes Hard Things Easy. He reminded me of a feature of ColdFusion that gets overlooked because it is so elemental to the language: the query variable.
In ColdFusion results that are returned from database get returned in a query variable. Query variables are an implementation of the iterator pattern and have a few special properties:
- Queries map columns from the database into easily addressable properties
- Queries also have the obvious properties named recordCount, currentRow, and columnList
- Queries have hooks to that cfloop and cfoutput can iterate over them
- Properties of queries can be addressed easily within an iterating process
That's all a really complicated way of describing what happens in this code:
<cfquery name="qry" datasource="cfartgallery">
SELECT firstName, lastname
FROM app.artists
</cfquery>
<cfoutput>
<p>#qry.recordCount# records returned with columns named: #qry.columnList#</p>
</cfoutput>
<cfoutput query="qry">
#currentRow# of #recordcount# - #FirstName# #lastName#<br />
</cfoutput>
You see, the first cfoutput tag didn't have a query attribute, so I had to prepend the properties recordCount and columnList with qry. Later when I was iterating over the query using cfoutput, I didn't have to bother prepending.
That's cool, and easy, but not outside the realm of what you can do in other languages. So let's get to what Kevin mentioned in his comment Query of Queries. Query of Queries refers to ColdFusion's ability to use a ColdFusion query as a table against which to perform SQL in another query. Want to get all of the records from the previous query that begins with A:
<cfquery name="anames" dbtype="query">
SELECT firstName, lastname
FROM qry
WHERE firstname like 'A%'
</cfquery>
Want to get all of the records that begin with E:
<cfquery name="enames" dbtype="query">
SELECT firstName, lastname
FROM qry
WHERE firstname like 'E%'
</cfquery>
Want to combine the A records and E records:
<cfquery name="uber" dbtype="query">
SELECT firstName, lastname
FROM anames
UNION
SELECT firstName, lastname
FROM enames
</cfquery>
That's right query of queries will let you do unions or joins too.
Now some of you might be questioning the usefulness of this. You think things like filtering, joins and unions are operations best handled by the database. You're right they do. But, imagine that I was pulling data out of separate databases, perhaps one in Oracle, and one in MSSQL? You couldn't do it in the database.
Also cfquery is not the only tag in ColdFusion that creates query variables. The others include (But I'm sure I'm missing some):
- cfdbinfo
- cfdirectory
- cffeed
- cfexchange tags
- cfldap
- cfsearch
- cfstoredproc
That means you can take any of the results of these tags and filter and group and join them just the way you can with a database. Want to see all of the files in your directory and are not .cfm files:
<cfdirectory action="list" name="files" directory="#ExpandPath('.')#" />
<cfquery name="nonCFFiles" dbtype="query">
SELECT *, directory + '/' + name as fullpath
FROM files
WHERE name not like '%.cfm'
</cfquery>
<table>
<cfoutput query="nonCFFiles">
<tr>
<td>#fullpath#</td>
<td>#size#</td>
<td>#datelastmodified#</td>
</tr>
</cfoutput>
</table>
That's pretty easy, and I think that qualifies as something not every solution can do. It opens up the door to a lot of other possibilities:
- Joining LDAP data to SQL Data
- Creating a union of multiple RSS feeds
- Joining data about stored files to SQL data
- Joining RSS feed data to Exchange users
ColdFusion queries, and query of queries are powerful features that make one of the most common jobs in web programming – displaying some kind of retrieved record – shockingly simple. Add to it that the number of entities addressable in a ColdFusion query includes many other sources than just the database, and you have one powerful feature that is not easily matched.
8 response s so far ↓
1 SanjayM // Feb 11, 2009 at 11:45 AM
2 Dan Laughland // Feb 11, 2009 at 1:08 PM
3 andy matthews // Feb 11, 2009 at 1:27 PM
Just an FYI about joining (unions) between two different RDBMS. I just asked our senior DBA here and he said that you can connect two RDBMS together, as long as you've got the names and paths and permissions correct.
4 Daniel Sellers // Feb 11, 2009 at 2:51 PM
For Instance:
SELECT name, zip
FROM users INNER JOIN address ON users.id = address.userid
will work fine as an SQL query but fails as a QoQ.
SELECT name, zip
FROM users, address
WHERE users.id = address.userid
being the QoQ equivalent.
I forget this every couple of months and have to go look it up in the CF docs. You also left off the ability to create new query objects from scratch with the query functions.
5 Terrence Ryan // Feb 11, 2009 at 4:03 PM
Now, I caution that I only have a superficial overview of LINQ and if someone who has a better understanding wants to disagree with me, feel free.
6 Magnus Thyvold // Feb 11, 2009 at 7:06 PM
You can even group on parts of field data such as date parts. I once took a year's worth of data and was able to create nice neat presentation of it by grouping on the Year, then the Month, then the Week, then created links of each item and then (getting carried away a bit) put the components of each item in a tool tip. Even as a novice CF programmer it was easy. It's like magic.
7 Jeff Porten // Feb 11, 2009 at 10:57 PM
8 Daniel Sellers // Feb 12, 2009 at 1:33 PM
I agree wholeheartedly about the power and ease that using actual SQL syntax in internal queries provides. I use this more and more often lately. It really is a fantastically useful feature in CF.
I do wish normal join syntax was supported but either way this allows for some very powerful data manipulation with fewer SQL server calls.
Leave a Comment