Filters and Other Goodies
Similarly, you can use SQL on a Query in order to filter data or select a subset of that data. Imagine extracting all books whose Title starts with the letters A-F. The following query would provide that data:
<cfquery dbtype="query" name="filteredBooks">
SELECT * FROM bookQuery WHERE Title >= 'A%' AND Title <= 'F%'
You might also just return a subset of the columns, or append an ORDER BY onto those results, which would also be legal. It is also possible to use some standard SQL aggregate functions on datasets held in Query objects; these commands include:
Watch Out for Some "Gotchas"
The main gotcha to using Queries is that you need to watch your SQL syntax. Remember that you are not writing SQL in the context of your database server anymore, so you need to avoid any database-specific syntax or extensions provided by database vendors. For example, say you are accustomed to using SQL Server's TOP clause (SELECT TOP 5 * FROM books) or MySQL's LIMIT (SELECT * FROM books LIMIT 5) to limit the number of rows returned from a database query. These keywords are specific to those database servers, and CF's SQL processor does not recognized them. Using them will produce errors.
Another thing you can't do with queries of Queries is use SQL commands other than SELECTno DELETEs, UPDATEs, or INSERTs allowed. Which syntax is allowed? The "Backus Naur Form (BNF) for CF's Query of Queries" describes the allowable syntax for this feature.
Performance is another area to watch out for, as the SQL execution in CF will in most cases not be as fast as that in a database. However, my personal benchmarking suggests that this probably won't be an issue unless you are handling very large datasets. If the difference between a 10ms and a 100ms execution time is a big deal for your particular application, or if you are handling very large datasets, benchmark your performance.
More CF Query Advantages
This article has focused on CF queries as flexible datastructures that can be manually assembled in code, but other aspects of this functionality also are worth mentioning. CF queries are not only for databases or for data assembled manually:
<cfpop> return Query objects as well. Given this functionality, you can further sort or filter these results in the ways described previously.
Finally, you can also combine data from disparate datasources using the Query object. For example, you could combine the results of a
<cfquery> results by doing something like this:
As such, queries provide a lingua franca for passing data around CF applications, no matter where that data was stored.