RSS Feed
Download our iPhone app
Browse DevX
Sign up for e-mail newsletters from DevX


No Datastructure? ColdFusion's Query Object to the Rescue

ColdFusion's Query object enables you to manipulate CF data using standard SQL syntax—even if that data doesn't come from a database.

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 Gardening
Acker, 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:

<cfquery dbtype="query" name="sortedBooks">
SELECT * FROM bookQuery ORDER BY LastName,FirstName,Title

The key here is the dbtype attribute of the <cfquery> 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.

Close Icon
Thanks for your registration, follow us on our social networks to keep up-to-date