Preface to this Series
As with most coding challenges, there are always near infinite ways to overcome them. To say one approach is better than another is nearly always a matter of perspective and goals, but what I use as my personal measuring stick is the spirit of a quote by the late great Buckminster Fuller. It reads, “When I am working on a problem I never think about beauty. I only think about how to solve the problem. But when I have finished, if the solution is not beautiful, I know it is wrong.” I have adopted this quote as my personal and professional creed when it comes to problem solving. It also happens to be the motivation for this series, as I hope to inspire my peers to let their coding be a reflection of who they are as people. After all, we are artists and coding is the expression of our talents.
If the solution is not beautiful, it is wrong. With that in mind, let’s take a look at the first item of discussion that has led to a lot of “wrong” solutions out there. It’s one of the most basic of things, yet so often is done in such an ugly manner: outputting complex query data.
The Power of <CFOUTPUT>
For the most part, what I am about to share will not apply when talking about simply outputting a query, but rather comes heavily into play when we are given the complex task of outputting composite data from several sources that must be grouped and summarized. That, as many of you know, is where things can start to get really ugly and, without an understanding of one of our beloved CFOUTPUT’s lesser used attributes, there’s little hope of giving it any elegance at all.
The Scenario
You are working on an application that manages projects for a large telecommunications company. Every project can be composed of several tasks, and each task can have multiple people assigned to it. Your challenge of the day is to produce a report showing all projects and their associated details. Here are the tables where your data is stored:
Here is the mockup you were given to duplicate for the report:
Now, time to build a solution. The more beautiful approach consists of two steps:
-
Getting creative with our SQL statement,
-
and nesting CFOUTPUT tags.
Creative SQL
The goal here is to retrieve EVERYTHING we need for our report in one query, the results of which will look like this:
Here is the SQL statement for returning a query set like that previously shown:
Tasks.Description, Tasks.CreatedDate AS taskCreationDate,People.Firstname, People.Lastname, People.Title,
People.Email
FROM ((((Projects INNER JOIN projectTasks ON Projects.projectID = projectTasks.ProjectID)
INNER JOIN Tasks ON projectTasks.TaskID = Tasks.taskID)
INNER JOIN taskAssignment ON Tasks.taskID = taskAssignment.TaskID)
INNER JOIN People ON taskAssignment.PersonID = People.personID)
INNER JOIN Roles ON taskAssignment.RoleID = Roles.roleID
ORDER BY A_ProjectName, B_TaskName, C_RoleName
Besides proper joins, the one thing about this SQL statement that is absolutely IMPERATIVE in order for us to be able to leverage our CFOUTPUT tag is the ORDER BY clause. The reason is this: Coldfusion will be processing our query sequentially, one row at a time starting at the top, and if everything isn’t in just the right order, we’ll get some unexpected and unpredictable results. If we DO have it in the right order, however, CFOUTPUT can do a lot of work in a few lines.
Nesting CFOUTPUT Tags
The second thing we’re going to do is something that many of you have seen CF complain about during your development: nest CFOUTPUT tags. Though usually it is true that you cannot nest CFOUTPUT tags, there is one situation where not only can they be nested, they should be: when utilizing the attribute GROUP.
Now that we have our data set, let’s resist that first impulse to use CFLOOP to output it and rather use CFOUTPUT, like this:
<h2>Projects</h2>
<ul>
<cfoutput query="qrydataset" group="a_projectName">
<l>
#a_projectName#
</li>
</cfoutput>
</ul>
By telling CFOUTPUT to output the query “qrydataset”, we’re telling it to loop over that query and for each line output whatever information we have specified between the opening and closing CFOUTPUT tag. BY SPECIFYING THE GROUP attribute, however, we’re telling CFOUTPUT to loop through the query, but only perform an output for EACH UNIQUE VALUE of the field specified in GROUP. The result then looks like this:
Projects
-
Baghdad Wireless Infrastructure Reconstruction Bid - Sensitivity Training for Executives
- Verizon Acquisition
Even though in reality those project names occur multiple times in our query result, the GROUP attribute has limited output to only unique values. Cool, eh?
Real quick, let’s take a look at what can happen if our query had not been sorted on the ‘A_ProjectName’ field:
Projects
-
Baghdad Wireless Infrastructure Reconstruction Bid - Sensitivity Training for Executives
- Verizon Acquisition
- Sensitivity Training for Executives
Random values show up wherever they were within the query; not good.
Okay, so now we’ve got CFOUTPUT giving us a nice list of projects by name. Let’s add in the tasks for each project. This is where it gets really cool, as we nest a CFOUTPUT tag:
<CFOUTPUT QUERY="qrydataset" GROUP="a_projectName">
<li>
#a_projectName#
<ul>
<CFOUTPUT GROUP="b_taskName">
<li>
#b_taskname#
</li>
</CFOUTPUT>
</ul>
</li>
</CFOUTPUT>
</ul>
(Notice that in the nested CFOUTPUT tag we did not have to re-specify the QUERY attribute)
It’s important to be able to visualize what’s happening here. The outer CFOUTPUT is the master and is looping over our entire query one row at a time starting with the first row. Using the GROUP attribute, we instructed the outer tag to only perform output upon encountering a unique value of ‘A_ProjectName’. Now, here is an important item to comprehend: Because the outer tag gives “scope”, or “limits” to any CFOUTPUT tags nested within it, our innermost CFOUTPUT tag is only going to cycle through those query rows that are in our current ‘A_ProjectName’ group. In other words, if our current unique value of ‘A_ProjectName’ is “Blah”, then our inner CFOUTPUT tag is only going to loop through those query rows whose ‘A_ProjectName’ is “Blah”. Once our ‘A_ProjectName’ value changes, let’s say to “Pooh”, our nested CFOUTPUT tag will once again be able to loop, but this time only over those query rows whose ‘A_ProjectName’ is “Pooh”. Here is what the results look like so far:
Projects
-
Baghdad Wireless Infrastructure Reconstruction Bid - Needs Analysis
- Remote Worker Recruitment
- Sensitivity Training for Executives
- Assemble Curriculum Committee
- Technical Writer Recruitment
- Train Instructors
- Course Marketing
- Course Screening
- Verizon Acquisition
- Assess Value of Verizon
- Assemble PR Committee
- IT Analysis
We’re not finished yet, though. We need to output the people assigned to tasks and their roles, so let’s add another nested CFOUTPUT!
<CFOUTPUT QUERY="qrydataset" GROUP="a_projectName">
<li>
#a_projectName#
<ul>
<CFOUTPUT GROUP="b_taskName">
<li>
#b_taskname#
<ul>
<CFOUTPUT GROUP="c_rolename">
<li>
#c_rolename#:
</li>
</CFOUTPUT>
</ul>
</li>
</CFOUTPUT>
</ul>
</li>
</CFOUTPUT>
</ul>
As in the last iteration of our CFOUTPUT code, our most inner nest was instructed to only perform its output functionality upon encountering a unique value of ‘C_RoleName’. Because it is nested within the second level CFOUTPUT tag, it will restart its looping every time a unique value for the second level loop is encountered, limited by that second CFOUTPUT to only applicable query rows . Here are the results:
Project
-
Baghdad Wireless Infrastructure Reconstruction Bid - Needs Analysis
- Business Analyst
- Remote Worker Recruitment
- HR Admin
- Needs Analysis
- Sensitivity Training for Executives
- Assemble Curriculum Committee
- Business Analyst
- Technical Writer Recruitment
- HR Admin
- Train Instructors
- Trainer
- Course Marketing
- Business Analyst
- Course Screening
- Trainer
- Assemble Curriculum Committee
- Verizon Acquisition
- Assess Value of Verizon
- Business Analyst
- Assemble PR Committee
- Business Analyst
- IT Analysis
- Developer
- Assess Value of Verizon
Let’s add one more nested CFOUTPUT, this time without a GROUP attribute, in order to output the individuals assigned to specific task roles, but without regard to unique values:
<CFOUTPUT QUERY="qrydataset" GROUP="a_projectName">
<li>
#a_projectName#
<ul>
<CFOUTPUT GROUP="b_taskName">
<li>
#b_taskname#
<ul>
<CFOUTPUT GROUP="c_rolename">
<li>
#c_rolename#
<ul>
<CFOUTPUT>
<li>
#firstname# #lastname# (#email#)
</li>
</CFOUTPUT>
</ul>
</li>
</CFOUTPUT>
</ul>
</li>
</CFOUTPUT>
</ul>
</li>
</CFOUTPUT>
</ul>
And here are our results now:
Projects
- Needs Analysis
- Business Analyst
- Shawna Easton (SE@bla.com)
- Bjorn Jager (BJ@bla.com)
- Business Analyst
- Remote Worker Recruitment
- HR Admin
- Maximus Minimus (MM@bla.com)
- HR Admin
- Assemble Curriculum Committee
- Business Analyst
- Joan Goodbody (JGB@bla.com)
- Business Analyst
- Technical Writer Recruitment
- HR Admin
- Shawna Easton (SE@bla.com)
- Joan Goodbody (JGB@bla.com)
- HR Admin
- Train Instructors
- Trainer
- Joan Goodbody (JGB@bla.com)
- Trainer
- Course Marketing
- Business Analyst
- Maximus Minimus (MM@bla.com)
- Bjorn Jager (BJ@bla.com)
- Simon Horwith (SH@bla.com)
- Business Analyst
- Course Screening
- Trainer
- Shawna Easton (SE@bla.com)
- Trainer
- Assess Value of Verizon
- Business Analyst
- Bjorn Jager (BJ@bla.com)
- Maximus Minimus (MM@bla.com)
- Business Analyst
- Assemble PR Committee
- Business Analyst
- Bjorn Jager (BJ@bla.com)
- Debbie Reynolds (DR@bla.com)
- HR Admin
- Desislava Nikolava (DN@bla.com)
- Business Analyst
- IT Analysis
- Developer
- Omar DuPuis (ODP@bla.com)
- Doug Boude (dougboude@gmail.com)
- Simon Horwith (SH@bla.com)
- Developer
Now, a few more items and some formatting, and we have the code to output the requested report:
<ul>
<cfoutput query="qrydataset" group="a_projectName">
<li>
<font size="+1" color="red">#a_projectName#</font> <strong>Created</strong>: #ProjectCreationDate#
<cfset TaskCounter=0>
<ul>
<cfoutput group="b_taskName">
<cfset FTEcounter=0>
<cfset TaskCounter = TaskCounter+1>
<li>
<font color="red">#b_taskname#</font> - #Description#
<ul>
<CFOUTPUT GROUP="c_rolename">
<li>
<strong>#c_rolename#</strong>
<ul>
<CFOUTPUT>
<cfset FTEcounter=FTEcounter+1>
<li>
#firstname# #lastname# (#email#)
</li>
</CFOUTPUT>
</ul>
</li>
</CFOUTPUT>
</ul>
#repeatString("nbsp; #firstname# #lastname# (#email#)
</li>
</CFOUTPUT>
</ul>
</li>
</CFOUTPUT>
</ul>
#repeatString("-",40)#<br>
&am
</li>
</CFOUTPUT>
</ul>
</li>
</CFOUTPUT>
</ul>
#repeatString("-",40)#<br>
<strong>Total FTEs Assigned for #b_taskName#</strong>: #FTEcounter#<br>
#repeatString("-",40)#
</li>
</cfoutput>
</ul>
<strong>Total Tasks for #a_projectName#</strong>: #TaskCounter#<br>
#repeatString("-",40)#
</li>
</cfoutput>
</ul>
Notice that our report required counts at different levels. We took advantage of the fact that every time an outer CFOUTPUT tag changes unique values, the nested CFOUTPUT tag starts over. Notice the “<CFSET [bla]Counter = 0>” calls. At those points in the code we are resetting our counters. Then within the nested CFOUTPUT where the value we’re concerned about counting is being output, we increment those same counters, later outputting them wherever needed. Cool stuff, eh?
And the final results:
Projects
- Baghdad Wireless Infrastructure Reconstruction Bid Created: 5/6/2005
- Needs Analysis - Analyze the specifics of the GSA RFP
- Business Analyst
- Shawna Easton (SE@bla.com)
- Bjorn Jager (BJ@bla.com)
Total FTEs Assigned for Needs Analysis: 2
---------------------------------------- - Business Analyst
- Remote Worker Recruitment - Recruit needed team members for remote assignment
- HR Admin
- Maximus Minimus (MM@bla.com)
Total FTEs Assigned for Remote Worker Recruitment: 1
---------------------------------------- - HR Admin
---------------------------------------- - Needs Analysis - Analyze the specifics of the GSA RFP
- Remote Worker Recruitment - Recruit needed team members for remote assignment
- HR Admin
- Maximus Minimus (MM@bla.com)
Total FTEs Assigned for Remote Worker Recruitment: 1
---------------------------------------- - HR Admin
----------------------------------------
- Assemble Curriculum Committee - Assemble committee to draft curriculum for STE tra
- Business Analyst
- Joan Goodbody (JGB@bla.com)
Total FTEs Assigned for Assemble Curriculum Committee: 1
---------------------------------------- - Business Analyst
- Technical Writer Recruitment - Recruit technical writers to finalize curriculum
- HR Admin
- Shawna Easton (SE@bla.com)
- Joan Goodbody (JGB@bla.com)
Total FTEs Assigned for Technical Writer Recruitment: 2
---------------------------------------- - HR Admin
- Train Instructors - Train instructors on course curriculum
- Trainer
- Joan Goodbody (JGB@bla.com)
Total FTEs Assigned for Train Instructors: 1
---------------------------------------- - Trainer
- Course Marketing - Market course internally to management
- Business Analyst
- Maximus Minimus (MM@bla.com)
- Bjorn Jager (BJ@bla.com)
- Simon Horwith (SH@bla.com)
Total FTEs Assigned for Course Marketing: 3
---------------------------------------- - Business Analyst
- Course Screening - Assemble sampling of executives to screen the cour
- Trainer
- Shawna Easton (SE@bla.com)
Total FTEs Assigned for Course Screening: 1
---------------------------------------- - Trainer
----------------------------------------
- Assess Value of Verizon - Perform an in depth analysis of Verizon financials
- Business Analyst
- Bjorn Jager (BJ@bla.com)
- Maximus Minimus (MM@bla.com)
Total FTEs Assigned for Assess Value of Verizon: 2
---------------------------------------- - Business Analyst
- Assemble PR Committee - Put together team of representatives to devise PR
- Business Analyst
- Bjorn Jager (BJ@bla.com)
- Debbie Reynolds (DR@bla.com)
- HR Admin
- Desislava Nikolava (DN@bla.com)
Total FTEs Assigned for Assemble PR Committee: 3
---------------------------------------- - Business Analyst
- IT Analysis - Analyze existing Verizon IT capabilities
- Developer
- Omar DuPuis (ODP@bla.com)
- Doug Boude (dougboude@gmail.com)
- Simon Horwith (SH@bla.com)
Total FTEs Assigned for IT Analysis: 3
---------------------------------------- - Developer
----------------------------------------
I hope that you found this information useful, and may I encourage each and every one of you to consider using this approach the next time you see a template starting to get long, ugly, and IO heavy when outputting complex and nested data sets.
You are not logged in, so your subscription status for this entry is unknown. You can login or register here.
A few things I noticed though. In the first paragraph under your SQL you spell imperative wrong.
In the first HTML code block from the SQL, your opening tag is incorrect, it's listed as .