hose new to ColdFusion may be disappointed to find a weaker array of datastructures than those supported by other languages they have used for Web programming, such as Java and C#. While CF’s array, list, and struct types meet most of the routine demands of Web application development, these datastructures have difficulty dealing with more tricky coding situations, such as advanced sorting and extraction of sub-sets.

When complex situations arise, however, CF does have a solution: the Query object. ColdFusion’s ability to “query a query” offers a powerful way to help compensate for its datastructure limitations by allowing sets of data to be manipulated?over and over, if necessary?according to SQL logic. Therefore, sorting, extraction, concatenation, and mathematical operations are possible. In addition, ColdFusion allows Query objects to be manually assembled in CF code, making them an option for any coding situation where a richer datastructure is useful.

Sorting the Old Way
Consider the following example: sorting a set of books read in from a file, where each row has four fields: FirstName and LastName (author’s, that is), Title, and ISBN. In many applications, you might sort this list of books by author name, which means you need to sort by last name first, then sort by first name for those rows where last names match. In addition, if an author has multiple books, you would list an author’s books alphabetically as well. So for a dataset like this:

Acker, Bernie, Learning to Barbeque Acker, Angie, Growing Fruit Trees Acker, Angie, Beginner's Guide to Gardening

The sorted results would need to look like this:

Acker, Angie, Beginner's Guide to GardeningAcker, Angie, Growing Fruit Trees Acker, Bernie, Learning to Barbeque

If you make each element a CF struct and then put the structs into another struct, you can sort by last name. The code to create the structs and add them all might look like this:

books = StructNew();book = StructNew();book["LastName "] = "Acker";book["FirstName "] = "Angie";book["Title"] = "Beginner's Guide to Gardening";book["ISBN"] = "ISBN333";books[book["ISBN"]] = book;// etc.StructSort( books, "text", "ASC", "LastName")

When you use StructSort(), what you get is not a sorted struct, but a sorted set of keys to the struct. So after running this code, you can access your books in last name order, but what about the need to order by first name, so that Bernie Acker comes after Angie Acker? Or the need to sort by book title, so that Angie Acker’s books come in alphabetical order? StructSort() can’t help you?you are in for lots of stinky CF code to get that sorting done.

A Better Way to Sort
Instead of using a complex array of structs, create a Query object and put your data from the file in there:

bookQuery = QueryNew("LastName,FirstName,Title,ISBN");newRow = QueryAddRow(bookQuery);QuerySetCell(bookQuery, "LastName", "Acker" );QuerySetCell(bookQuery, "FirstName","Angie" );QuerySetCell(bookQuery, "Title","Growing Fruit Trees " );QuerySetCell(bookQuery, "ISBN","ISBN222" );// etc.

Now when you want to do your complex sort, all you have to do is use SQL to do the sorting:

SELECT * FROM bookQuery ORDER BY LastName,FirstName,Title

The key here is the dbtype attribute of the tag. It tells CF to use an existing Query as the datasource, which you provide in the SQL FROM clause (in this case, the Query bookQuery). You can see the power of this type of sorting: you can use SQL syntax to easily order and reorder collections of data by any field or set of fields.

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:

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: Min(), Max(), Avg(), Count(), and Sum().

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 SELECT?no 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: , , and 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 with results by doing something like this:

SELECT LastName,FirstNameFROM databaseQuery UNIONSELECT LastName,FirstNameFROM textSearchQuery

As such, queries provide a lingua franca for passing data around CF applications, no matter where that data was stored.