Introduction to Coldfusion Data Structures Part IV – Queries

Build a Query Manually

If you really want to build a query manually, you can use a combination of several functions to do so. To start with, we'll need to discuss some of the functions that are built into Coldfusion for manipulating queries.

First of all, we have to create a query object. This is easily accomplished with the QueryNew() function. This function takes a comma delimited list of the columns that need to be in the query.

<cfset myQuery = QueryNew("id, name, username, password")>

Now, we have an empty query. It lists the columns in the function parameter, but lists no rows at all. A query without data isn't much use to us, so we'll add some data using the QuerySetCell() function. This function takes the name of the query, the name of the column, the value of the column, and the row number to insert this information into. We can add our first user like so:

<cfset temp = QuerySetCell(myQuery, "id", "1", 1)>  
<cfset temp = QuerySetCell(myQuery, "name", "John Doe", 1)>  
<cfset temp = QuerySetCell(myQuery, "username", "JDoe", 1)>  
<cfset temp = QuerySetCell(myQuery, "password", "test", 1)>

After this, we can output our query to the screen and see that it really is a query object with the data we've entered.

<cfoutput query="myQuery">  
 #id# : #name# : #username# : #password#<br>  
</cfoutput>

Now, you may be thinking that you don't see much of a point in creating your own query, and, in fact, in most cases you won't need this. But, here's a scenario in which you might find some use for this functionality. I won't provide any code for this particular case, since it's actually fairly involved, but I'm sure you can dream up all sorts of ways to apply this technique.

Imagine that you are building an online store for an affiliate that provides product streams via a Web service (one very large affiliate comes to mind). When you call the Web service, an XML document is returned to you. When XML documents have been parsed, they are returned as structures nested inside arrays nested inside structures, etcetera, etcetera, etcetera.

We can create a query object that will be easier to manipulate than a convoluted series of structures and arrays using the above methodology. In fact, I just happened to find this tutorial, which will show you how to accomplish this particular task (it doesn't use Amazon's Web services, but the concept is the same and easily portable).

You can also add a column to a query using the QueryAddColumn() function. For this function, you specify the query name, the column name, and the name of an array that contains the data with which you want to populate the column. To prove that this actually does happen in real life, take a look at this forum post. This is a real life situation where the QueryAddColumn() function could come in handy (ignore any syntax errors in there. It was whipped up on the fly without testing…). So, there are definitely real life situations where you may need to use these functions.

Querying a Query

Sometimes, you may need to pull a lot of detailed data from a database and then retrieve aggregate data based on the detailed information. In this case, Coldfusion's ability to use a query as a data source comes in quite handy. Now, as forum member mrhatch pointed out to me in the previously referenced forum thread, this functionality is referred to as CFSQL (I just called it a query of a query before).

Let's take a look at a situation in which this might be useful. In our existence as programmers, we often find ourselves creating reports. Yes, we hate to do it, but it's a necessary evil if we want to pay our bills. In many cases, users want the ability to see aggregate data as well as detailed information. In this case, we have a few options.

1. We can get a query containing the details and, as we output the query, we can pull aggregate data from the database using an identifier contained within the detail query.


2. We can output the query and manually compile our aggregate results as we do so, performing any needed calculations before finally outputting to the screen.


3. We can use CFSQL to avoid both of the above scenarios and, at the same time, retrieve the needed summary information.

For our tutorial, we'll go with the third option since, of course, it demonstrates the point here. We'll say that we have a query contains the following data:

This data is in a query called GetDetails and it shows a detailed listing of both our company's sales people, the orders they fulfilled, and the dates and clients for which the orders were fulfilled. What we want to see is a prettier version of this, in which each sales person's name is a header and, beneath that, appears a list of their sales. Then, at the bottom of each section, we want to see the sales person's total sales.

Challenge: Try to create the query above using only the QueryNew, QueryAddRow, and QuerySetCell functions. This way, you don't have to create a database, a data source, and the table in order to follow along with the other examples.

Let's start by outputting our query. This will give us the detailed listing of how our sales guys did:

<cfset curUserID = GetDetails.UserID>  
<cfoutput query="GetDetails">  
  <cfif CurrentRow lt RecordCount>  
     <cfset nextUser = GetDetails.UserID[CurrentRow + 1]>  
  <cfelse>  
     <cfset nextUser = "">  
  </cfif>  
  <cfif curUserID neq UserID or CurrentRow eq 1>  
     <hr>  
     #UserID# #UserName# (#FullName#) : <br>  
  </cfif>  
  <blockquote>  
     #OrderID# - #CustomerName# was sent on #DateFormat(SentDate, "mm/dd/yyyy")# #TimeFormat(SentDate, "hh:mm:ss tt")#<br>  
  </blockquote>  
  <cfif NextUserID neq UserID>  
     <cfquery name="GetCount" dbtype="query">  
        SELECT Count(OrderID) AS OrderCount  
        FROM GetDetails  
        WHERE UserID = #UserID#  
     </cfquery>  
     <blockquote>  
        <strong>User Total: #GetCount.OrderCount#</strong>  
     </blockquote>  
  </cfif>  
  <cfset curUserID = UserID>  
</cfoutput>

Okay, there's a lot of code here to cover, so let's get right into it. First, we only want to output each sales person's name and ID once. So, we have to track what the current user ID is, and what the next user ID is. We track our current user with the curUserID variable. We then dive head-first into our query.

The first CFIF statement looks to see if we're on the last record in the query. If we are, our next user is a null string. If we're not, we treat the UserID column as an array, and we get the value of the UserID column in the next row.

Now, we want to know if it's time to display the user's name. We know that, if this is the first row in the query, we'll need to. But, if the curUserID variable is not the same as the UserID variable (which comes straight from the query), this means we have advanced to another user's records. At this point, I should emphasize the importance of ordering your columns by your identifier, in this case UserID. If we are on another user's records, we output that person's name, user name, and user ID.

Now, we simply display the order ID, customer name, and the date on which the order was sent. Notice that these bits of data are displayed no matter where we are in the query.

Next, we get to the interesting part: we look to see if this is the last row for this user. If it is, we use the magic CFSQL. Notice a couple of odd things about this query. First of all, we don't specify the "datasource" attribute; we specify the "dbtype" attribute and assign it the "query" value. Second, we aren't selecting from a table; we're selecting from a query. But, when you think about it, there really isn't a big difference between a table and a query. A query has the same structure as a table, with rows and columns.

Now, we can limit our record set from the CFSQL query just as we might limit our record set from a MySQL database or a SQL Server database. We use the WHERE clause to specify that we only want to get the current user's records. Then, we output the record count of the query. Finally, we set the curUserID variable to the current UserID from the query.

Using Coldfusion 5, I've personally had shaky results using aggregate functions like COUNT in combination with WHERE clauses in CFSQL queries. Coldfusion MX seems to have rectified the problem, since I tested all of this code on CFMX 6.1. If you're using Coldfusion 5, you can just select all or a single field from the query, and use the (QueryName).RecordCount variable to figure out how many sales this user had, for instance, or whatever it is you're trying to figure out.

Conclusion

Queries are more than just the results you get from a database. In Coldfusion, queries offer very simple ways to display and manipulate data. You can convert other data structures (like XML documents) into queries, and display them in a much simpler fashion. You can glean aggregate data from detailed queries without additional database hits using CFSQL. You can access specific rows and columns in a query by treating it as a structure containing a series of arrays. You can display a query dump using the ColumnList variable attached to each query. And you can get a listing of directories and files and access it just as you would a query.

That concludes our Introduction to Coldfusion Data Structures series. There are, of course, all sorts of data structures that can be put to excellent use by a creative developer, and I hope that this series has helped you to see some of the ways in which Coldfusion can be ever-so-easily manipulated to meet today's business challenges.