Queries are the Secret Sauce of ColdFusion
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 responses so far ↓
1 SanjayM
Very cool post Terry. it's nice to be reminded or refreshed on things that we forget about Cf. Keep 'em coming.2 Dan Laughland
This sounds a lot like LINQ, the relatively new query language in .NET. Do you know how that compares to ColdFusion's query abilities?3 andy matthews
Great post.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
Have to watch those joins in QoQs. You cannot do INNER or OUTER joins in the same way you do in SQL on the server side. Those kinda of joins must be done in the WHERE clause.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
@Dan I haven't done an in depth look. What strikes me as different, and IMHO better about CF is that LINQ (Language-Integrated Query) is a language for dealing with database queries. On the other hand ColdFusion allows you to punch a hole in it's syntax to write in SQL, the language you already use for queries (for better or worse.)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
You didn't mention the great ease with which one can work with grouped data. This comes up a lot where you have one master record related to several records in another table in a one to many relationship. cfquery or cfloop makes it very very easy to output without repeating data common to multiple rows and therefore gives you a lot of control over the presentation of your data. Faced with a similar situation in PHP, I searched high and low to find out how to do it and was told basically that you can't. (You can but it is quite complicated). The "easy" PHP solution was to query one table, loop over it to output the row AND query the other table to get rows matching the current row from the first table and output that in a nested loop(ie, another query for each row in the first query. The result is dozens or even hundreds(or more) database hits instead of just oned needed by CF.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
Okay, so some light is beginning to dawn. Most of the tools I use are best at working with internal databases which are meant to replace legacy systems. What you're describing here is a middleware tool when the databases continue to exist elsewhere, yes? I've been using Lasso for such things, and you can count the number of times I've done so by reviewing the bloody headprints on the wall. So please keep up the tutorial.8 Daniel Sellers
@TerrenceI 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