Accessibility
 
Home / Developer Center / ColdFusion Developer Center /

ColdFusion Article
Icon or Spacer Icon or Spacer Icon or Spacer
Sue Hove
 
Sue Hove
Director of Instructor Readiness
www.macromedia.com
 
Building Advanced Queries in ColdFusion MX


Database connectivity is a key ColdFusion feature. The most common data source is a relational database. This article assumes that you already know how to perform standard queries in ColdFusion and that you’re comfortable with basic SQL statement syntax. This article teaches how to perform advanced queries and retrieve and organize data from a data source. You’ll learn how to:

  • Loop over queries with the cfloop tag
  • View data structure contents with the cfdump tag
  • Suppress repeated data with cfoutput grouping.
  • Query a query or a result set
  • Cache queries for increased performance

This article covers just a small portion of Advanced ColdFusion MX Development, an instructor-led course that Macromedia Authorized Training partners offer. If you like what you see and want to get the full experience, find a Macromedia Authorized Training Partner near you. Only Macromedia Authorized Training locations can give you thoroughly trained Macromedia certified instructors. You can learn more in a few days with a qualified trainer than you can learn in a year with a book. Get the skills you need to get ahead with Macromedia Training and Certification.

System Requirements
To use this tutorial, you must have:

Setting Up the Hands-on Files
You may wish to simply read this article, or follow along and perform the hands-on steps. To follow along, setup your computer with the steps below.

Install the example files:

  1. download download the example files (900 KB ZIP file).
  2. Double-click the self-extracting executable and install the files in your ColdFusion MX webroot (C:\CfusionMX\wwwroot is the default).
    Note: From here on, I will refer to this location as http://cfmx_webroot/.
  3. Verify that cfmx_webroot\mmcourses\acfd600\ installed correctly by locating the directory in Windows Explorer.

Create a Dreamweaver MX site:

  1. Open Dreamweaver MX.
  2. Select Site - New Site… from the Menu bar to create a new site.
  3. Select the Basic tab.
  4. Enter the following information:
    • Name: AdvQueries. Click Next.
    • Select "Yes, I want to use a server technology." Select ColdFusion from the pop-up menu for the server technology. Click Next.
    • Edit and test locally, storing your files at cfmx_webroot\AdvQueries\.
    • URL to browse to the root of your site http://localhost:8500/mmcourses/acfd600/
      Note: If you are using your own web server, remove the 8500 port from the URL
      Click Test URL to test your settings.
    • Select "No, do not copy files when I am done editing."
    • Click Done to create an initial cache of your site.

Create ColdFusion server mappings, data sources, and Verity collections:

  1. Disable your ColdFusion Administrator Password. You must disable your ColdFusion Administrator password only to set up the data sources, mappings, and verity collections. To disable the password, open ColdFusion Administrator and click CF Admin Password. Uncheck Use a ColdFusion Password. Click Submit Changes.
  2. Browse the page at the following URL:
    http://127.0.0.1:8500/mmcourses/acfd600_setup/acfd600_setup.cfm
    This page can take several minutes to complete.
  3. If you configured ColdFusion server to timeout long running requests, you can run the setup from the following URL instead:
    http://127.0.0.1:8500/mmcourses/acfd600_setup/pieces/
    Browse each of the files in order.
  4. Enable your ColdFusion Administrator password again, if you wish to do so.

Test the course files installation:

  1. Open up a browser and type in the following URL: http://localhost:8500/mmcourses/acfd600/solution/home/index.cfm.
    Note: If you see the Coffee Valley Login page, you installed the course files in the correct directory.
  2. Login to the application using:
    • User ID: training
    • Password: coldfusion

The Query Object
Most ColdFusion programmers first use the query object in ColdFusion. These objects hold rows and columns of data. You can create query objects in several ways:

  • When a cfquery tag retrieves data into a result set
  • Using other ColdFusion tags such as cfsearch and cfhttp
  • Using query functions such as QueryNew()

Looping Over Queries
You can loop over the data in a query using either:

  • <cfoutput query="queryname">
  • <cfloop query="queryname">

In each case, ColdFusion loops through all the code contained within the tag once for each row in the query object. To display data in the loop, use the cfoutput tag; to primarily execute logic, however, use the cfloop tag.

Nesting query output tags
The cfloop tag is a flexible ColdFusion tag that lets you repeat a block of code until it meets a specific condition. For instance, you might loop through all query results. The cfloop tag below uses the query attribute to loop through a query object:

 
<cfoutput>
  <cfloop query="qMyQuery">
		#qMyQuery.columnname#<br>
  </cfloop>
</cfoutput>
Nesting cfoutput tags will cause a syntax error. Instead, use the cfloop tag inside query-driven cfoutput tags. To nest output queries, use the following code: 
<cfoutput query="q1">
	#q1.columnname# 
  <cfloop query="q2">
		#q2.columnname#
  </cfloop>
</cfoutput>
Note: You can use nested cfoutput tags to group processing, which I cover later in this article.

The following figure shows the display for one query being used inside another. In this case, one query holds the questions while the second query holds the set of possible answers.
Figure 1. The output from a nested query
Figure 1. The output from a nested query
 

Using the cfdump Tag
Use the cfdump tag to visually represent a data structure. The cfdump tag displays data structures as color-coded HTML tables.

The basic syntax is as follows:

 
<cfdump var="#cfvariable#" label="Label for Dump">
The cfdump tag produced the following table for a query
Figure 2. The cfdump tag produces the query object visually
Figure 2. The cfdump tag produces the query object visually
 
Exercise: Using cfdump to display query results and loop over a query
  1. Open wt2-1.cfm in cfmx_webroot\mmcourses\acfd600\walk.
  2. In the page body, use the cfdump tag to display the query using the following attributes:
    var: #qList#
    label: Class Rosters
  3. Save the document and browse it at http://127.0.0.1:8500/mmcourses/acfd600/walk/wt2-1.cfm. Look at how the cfdump output organizes the data by the SQL query’s ORDER BY clause.
  4. Return to Dreamweaver MX.
  5. Comment the cfdump tag.
  6. After the comment, create a cfoutput tag with the following attribute:
    query: qList
  7. After the cfoutput tag, display the instructor’s first and last names inside <h1> tags.
  8. Following the instructor’s name, display the course name inside <h2> tags.
  9. Display the students’ first and last names after the course name using an unordered list. The completed code should appear as shown here:
    <cfoutput query="qList">
   <h1>#qList.i_fname# #qList.i_lname#</h1>
   <h2>#qList.course_name#</h2>
   <ul>
      <li>
         #qList.s_fname# #qList.s_lname#
      </li>
   </ul>
</cfoutput>
  10. Save the document and browse it. You will see many unnecessarily repeated pieces of data.

Grouping and Summarizing Data
When your query joins two tables with a master-detail relationship, the query output repeats the master table column values in the result set. You can suppress the repeated data in your output by using the cfoutput group attribute—this is sometimes called grouping output. You can also group the data itself, in addition to grouping the display. By grouping data, you summarize information, such as a sum for a set of rows in a result set.

Grouping Output Using CFML
To use cfoutput with the group attribute, you must do the following in your SQL query:

  • Use the SELECT statement to get the desired information from the database.
  • Include the ORDER BY clause in the query to order the data. Omitting the ORDER BY clause causes the incorrect grouping.
  • Use the cfoutput tag with a query attribute and a group attribute value that matches the first column listed in the ORDER BY clause.
  • If you need multiple levels of grouping, use nested cfoutput statements with only the group attribute. The group attribute values must follow the order of the column names listed in the ORDER BY clause in your SQL query. If you use another query attribute in the nested cfoutput tags, an error will occur.

The following query retrieves the student names and course names:

 
<cfquery name="qGetStudents" datasource="acfd600_walk">
	SELECT    course.course_name, student.s_fname, student.s_lname 
	FROM	    course, student, class_roster 
	WHERE   student.student_id=class_roster.student_id 
	AND	    class_roster.course_id=course.course_id 
	ORDER BY  course_name, s_lname, s_fname
</cfquery>
The query and output below show the code for displaying partial query output: 
<table border="1">  
  <tr>     
    <th>Course</th>    
    <th>Student Name</th>  
  </tr>  
  <cfoutput query="qGetStudents">    
    <tr>       
      <td>#qGetStudents.course_name#</td>      
      <td>#qGetStudents.s_fname#
        #qGetStudents.s_lname#</td>
    </tr>
  </cfoutput>
</table>

Which appears as follows:
Figure 3. Partial query output
Figure 3. Partial query output.
 
Notice that the course name repeats. You can suppress the course name from displaying for each student using the cfoutput group attribute. The partial output and corresponding code for the grouped query is as follows: 
<table border="1">  
<tr>
    <th>Course</th>
    <th>Student Name</th>
</tr>
  <cfoutput query="qGetStudents" 
             group="course_name">
   <tr>
     <td>#qGetStudents.course_name#</td>
     <td>
       <cfoutput>           
         #qGetStudents.s_fname# #qGetStudents.s_lname#<br>      
       </cfoutput>  
     </td>    
   </tr>  
 </cfoutput>
</table>

Which appears as follows:
Figure 4. Grouped query output.
Figure 4. Grouped query output.
 
Exercise: Grouping Query Output
In this exercise, use the cfoutput group attribute to group data returned from a query. You will build a report that lists the instructors, the classes each instructor teaches, and the students in each class.
  1. Return to wt2-1.cfm in cfmx_webroot\mmcourses\acfd600\walk.
  2. Save the document as wt2-2.cfm in the same directory.
  3. Locate the cfoutput tag, and add the following attribute:
    group: i_lname
  4. Create a nested cfoutput tag around the <h2> tags, down to and including the </ul> tag, using only the single group attribute:
    group: course_name
  5. Create a second, nested cfoutput tag around the <li> and </li> tags. Do not use any attributes.
  6. Check to ensure the code appears as shown here:
 
<cfoutput query="qList" group="i_lname">
   <h1>#qList.i_fname# #qList.i_lname#</h1>
   <cfoutput group="course_name">	
      <h2>#qList.course_name#</h2>
      <ul>
      <cfoutput>
         <li>
            #qList.s_fname# #qList.s_lname#
         </li>
      </cfoutput>
      </ul>
   </cfoutput>
</cfoutput>
  1. Save the document and browse it. You should see the instructors’ names in header 1 style, the classes they teach grouped under their names, and finally, a bulleted list of students in each class.
  2. Return to Dreamweaver MX and close the document.

Grouping data using SQL
Using SQL, you can group data and return information about the entire set of data—such as the sum or a column average. By default, the query calculates these aggregate values from all the rows in a column—which you would use if you wanted to calculate the average for all courses.
You can also calculate separate aggregate values for data subsets—for example, if you wanted to calculate each course’s average. To do this, use the SQL GROUP BY clause. You can also filter the aggregate rows with a HAVING clause.

Aggregates
Use SQL aggregate functions summarize calculations for a set of rows; these rows could be all the rows, only those specified in a WHERE clause, or groups of rows.
The following table shows five common SQL aggregate functions:
Aggregate Description
AVG() Returns the average for a set of values.
COUNT() Returns the number of rows in a set of values.
MAX() Returns the highest value in a set of values.
MIN() Returns the lowest value in a set of values.
SUM() Returns the sum for a set of values.

You can calculate the overall average, minimum, and maximum scores for all the students in all the courses, as shown here:

 
<cfquery name="qGetScores" datasource="acfd600_walk">
	SELECT	AVG(score) AS AvgScore,
		MAX(score) AS MaxScore,
		MIN(score) AS MinScore
	FROM 	class_roster			
</cfquery>
This query returns only one record. The output is as follows:
Figure 5. Aggregating data.
Figure 5. Aggregating data.
 

Grouping data with GROUP BY
By default, aggregate functions work on all the rows in a column. To calculate aggregates for subsets of data, use the SQL GROUP BY clause.

In the class roster example, you can use SQL grouping and aggregate functions to calculate the average, minimum, and maximum scores for each course. You must include all columns in the SELECT statement in the GROUP BY clause, excluding the aggregate functions.

The code is shown here:

 
<cfquery name="qGetScores" datasource="acfd600_walk">
	SELECT 	course.course_name,
			AVG(score) AS AvgScore,
			MAX(score) AS MaxScore,
			MIN(score) AS MinScore
	FROM 		class_roster, course
	WHERE 		class_roster.course_id=course.course_id
	GROUP BY	course.course_name	
             ORDER BY	course_name	
</cfquery>
Note: The ORDER BY clause is not necessary in this example; the query only uses to return the course alphabetically.

Use the cfoutput tag (with a query attribute) to display the data. You will see something similar to the following:
Figure 6. Aggregating subsets of data.
Figure 6. Aggregating subsets of data.
 

Filtering Groups in SQL
To filter the returned rows from a grouped statement, use the HAVING clause. You can think of the HAVING clause as a WHERE clause for groups. The HAVING clause can use all the operators the WHERE clause uses, including wildcards and multiple conditions.

The following example returns only courses that have an average score less than 80: 

<cfquery name="qGetScores" datasource="acfd600_walk">
	SELECT 	course.course_name,
			AVG(score) AS AvgScore,
			MAX(score) AS MaxScore,
			MIN(score) AS MinScore
	FROM 		class_roster, course
	WHERE 		class_roster.course_id=course.course_id
	GROUP BY	course.course_name
	HAVING 	AVG(score) < 80
	ORDER BY 	course_name
</cfquery>
Note: As you can see in this code, some databases do not allow the HAVING clause to use an alias. Also note that the HAVING clause must be before the ORDER BY clause, otherwise the query will throw an error.

The code for displaying data remains unchanged, but changing the query with the HAVING clause produces the following output:
Figure 7. Filtering an aggregate data set
Figure 7. Filtering an aggregate data set
 

Exercise: Grouping and Summarizing Data with SQL
In this exercise, you display the number of students in each course using SQL aggregate functions and a GROUP BY clause. You also filter for courses with low enrollment using a SQL HAVING clause.

  1. Open wt2-3.cfm in cfmx_webroot\mmcourses\acfd600\walk.
  2. In the body of the document, use the cfdump tag to display the query using the attributes shown here:
    var: #qGetStudents#
    label: Student List
  3. Save the document and browse it. The cfdump tag results showing the course and the number of students in the course.
  4. Return to Dreamweaver MX.
  5. Modify the SQL statement so only classes with two or fewer students are displayed. Use a HAVING clause, and put it before the ORDER BY clause.
  6. Save the document and browse it. Only classes with two or fewer students appear.
  7. Return to Dreamweaver MX and close the document.

Querying a Query
Often after you receive data from a query, you want to use the same data or a subset of the data, but present it in a different format, such as sorting it by a different column. ColdFusion 5 was the first version that allowed you to run a query against an existing query.

Query a query when you want to:

  • Reorder data. You have one set of data but need to present it or summarize it in many different ways. The user may want to sort the same data in different ways.
  • Create drill-down interfaces. You can query the database for all the records and then cache the query (this article covers it later). When the user drills down for details on a particular record, you can query the existing query without querying the database again.
  • Create next-n interfaces. You can query the database for all the records and then cache the query (covered later in this unit) so each page displays only a subset of the query records, without having to query the database on every page.
  • Combine record sets. You can perform joins and unions on query objects from different data sources to create one query object that you can manipulate. This lets you combine data from different data sources.
  • Graph data. You can query the data again to find and graph subsets and data summaries.

Using the dbtype attribute
To query an existing query, use the cfquery tag with the following rules:

  • Set the cfquery tag attribute dbtype="query".
  • Do not specify a datasource attribute.
  • In the SQL FROM statement, use the name of a query instead of specifying a table.

For example, if you have the following query:

 
<cfquery name="qNamesByFirst" datasource="acfd600_walk">
	SELECT		s_fname, s_lname 
	FROM		student 
	ORDER BY  	s_fname 
</cfquery>
You can query the qNamesByFirst query as follows: 
<cfquery name="qNamesByLast" dbtype="query">
	SELECT		* 
	FROM		qNamesByFirst 
	ORDER BY  	s_lname 
</cfquery>

When you query a query, you can also use:

  • Generated or computed columns
  • The results of one or two queries in the SQL statement

When you query an existing query, you can use a subset of SQL statements, functions, and operators.

Note: Read the ColdFusion MX documentation for detailed rules and restrictions for each type of operator.

Exercise: Group SQL Data
In this exercise, you will query a query to determine the number of students in each class.

  1. Open wt2-2.cfm in cfmx_webroot\mmcourses\acfd600\walk.
  2. Save the document as wt2-5.cfm in the same directory.
  3. Just below the line of code <cfoutput group="course_name">, insert a cfquery with the following attributes:
    name: qNum
    dbtype: query
  4. Inside the cfquery tag, write the SQL statements to perform the following actions:
    • Select the COUNT for all students in the course that match the course that currently displays in the loop. Assign an alias of numStudents to the count.
    • Query the qlist query.
    • Filter the query results with a WHERE clause that compares the course_id to the course_id from the qList query.
  5. After the course name, within the <h2> tags, display the number of students for the course. Be sure to include a label for the number.
  6. Save the document and browse it. The output should appear as it did in Walkthrough 2-3, but also display the number of students for each class.
  7. Examine the debugging information. Look at all the queries that executed; all but the first were queries of an existing query.
  8. Return to Dreamweaver MX.

Query Caching
When a page processes, most of the processing time occurs in the database. To avoid constantly querying the database, use query caching, which saves queries in the application server’s memory.

Query caching is ideal when:

  • The data changes little, such as product catalog items or a list of countries.
  • Your application uses the data on multiple pages.
  • Multiple users use your data.

Caching strategies
You can cache a query in two ways:

  • Cache it at the query level using a caching attribute in the cfquery tag.
  • Save it in a persistent scope variable, such as an application variable.

This article only covers the first method.

Query-based caching
Use query-based caching when you want to cache a particular query for a specific period of time. To implement this type of caching, use one of the following cfquery attributes:

  • cachedwithin: To specify a relative cache time.
  • cachedafter: To specify an absolute cache time.

The following table lists the pros and cons of query-based caching:

Benefits Drawbacks
Can dramatically enhance performance Uses unknown and uncontrollable amounts of memory
Easy to use Cannot explicitly manage it
Well-suited for dynamic queries Cannot easily flush it

Using the cachedwithin Attribute
Use the cachedwithin attribute to specify a time period to cache the query. The following query uses the same data for one full day before the database refreshes it:

 
<cfquery name="qGetCountry datasource="acfd600_solution"
         cachedwithin="#CreateTimeSpan(1,0,0,0)#">
	  SELECT 	CountryID, CountryName
	  FROM 	Country
	  ORDER BY 	CountryName
</cfquery>

When you cache a query, you can reuse it by using the cfquery tag with all of same query information as the cached tag—the only difference is that the query does not query the database again, it just returns the cached result set:

  • The values for the name and datasource attributes must be the same and are case sensitive.
  • The resulting SQL statements must be the same, including spaces, returns, tabs, and so on. In addition, the SQL statements are case sensitive and must be the same.

If any of this information differs, ColdFusion does not use the cached query results. You can, however, change the value of the cachedwithin or the cachedafter attribute, and still use the same cached query.

Using the cachedafter Attribute
Use the cachedafter attribute when you want to refresh the query at a certain time each day. For instance, if you update data nightly between 12:00 AM and 2:00 AM, you could refresh the query at 2:00 AM every day, and use that cached query until 12:00 AM the next day. Often you will use these functions with the cachedafter attribute: CreateDateTime( ), Year( ), Month( ), and Day( ).

Here is the code for a cfquery tag that caches the query and updates it at 2:00 AM every day:

 
<cfquery name="qGetCountry" datasource="acfd600_solution"
cachedafter ="#CreateDateTime(Year(Now()),
                              Month(Now()),
                              Day(Now()),
                              2,0,0)#">
Note: The cachedafter attribute in this code breaks the day into two parts. From 12:00 AM to 2:00 AM, the server does not cache anything. The first time any user uses the query after 2:00 AM, the server refreshes the cache and uses it until 12:00 AM that day.

Flushing Cached Queries
You can immediately refresh cached query contents for a query that uses the cachedwithin or cachedafter attribute by using all zeros in the CreateTimeSpan( ) function values, as shown here:

 
<cfquery name="qGetCountry" datasource="acfd600_solution"
         cachedwithin="#CreateTimeSpan(0,0,0,0)#">
	  SELECT 	CountryID, CountryName
	  FROM 	Country
	  ORDER BY 	CountryName
</cfquery>
Exercise: Using Query-Based Caching
In this exercise, you cache a query.
  1. Return to wt2-5.cfm in cfmx_webroot\mmcourses\acfd600\walk.
  2. Save the document as wt2-6.cfm in the same directory.
  3. Add a cachedwithin attribute to the qList query and set it equal to 1 minute. Remember to use the CreateTimeSpan( ) function.

    Note: You are using such a short time so you can see the query caching timeout.

  4. Save the document and browse it. In the debugging information, look at the execution time for the qList query.
  5. Refresh the page. Look again at the execution time for the qList query in the debugging information. You should now see that it is a cached query. If you wait one minute, you will see the cached query time out and then execute again.
  6. Return to Dreamweaver MX and close the document.

Using SQL in ColdFusion gives you powerful ways to retrieve and organize data. As you have seen, you can perform more difficult programming logic and return more advanced queries. This article explained only a small portion of what you can do with ColdFusion MX.

To see more, take the Advanced ColdFusion MX Development instructor-led course at a Macromedia Authorized Training Partner near you.

Training Courses
About the author

Sue Hove is the Director of Instructor Readiness at Macromedia, Inc. She has worked with Allaire and now Macromedia since October, 1996 and has worked on the ColdFusion authorized curriculum. She has a computer science degree and a background in relational database design and implementation, corporate training, and consulting.

Submit feedback on our tutorials, articles, and sample applications.