In a previous article for ColdFusion Developers Journal I described a ColdFusion component (CFC) that I wrote to report on what search terms visitors used on a site's search facility when visiting the Web sites I managed (February 11, 2005 - http://cfdj.sys-con.com/read/48234.htm).
My work is largely comprised of moderate-traffic sites hosted on shared servers and, while most hosting companies offer fairly robust statistical reporting, including well-articulated reports on the referring keyword terms used to enter a site from a search engine, I wanted a report that would show in a simple and easily readable way what terms had been entered in the search facilities (usually Verity-powered) that I had created for use within the site.
One method of the component recorded each search term used when it was used in a data table, and other methods called and displayed a time-sorted count of the most frequently used search terms.
That turned out to be so handy for me and my clients that I crafted another similar CFC that reports on which data records are being called into a detail page so the client and I can see, for instance, what performing arts events are most looked at or e-mailed about.
Since the same collection of records can be called from different calling pages and into different presentations of the details - for instance, one page of event listings can call a detailed page regarding one event, or a detail page itself can call a page to "Email a friend" - this reporting component needed to have a field in its data table for the calling page as well as the record called.
Displaying that information is a classic use of the "group" attribute of ColdFusion's <cfoutput> tag, which specifies the query column to be used when retrieving a record set that's ordered on a query column.
For our calling page CFC, the reporting query looks like this:
<cfquery name="recordcount" datasource="#thedatasource#">
SELECT thecallingscript, thecalledrecordtitle, COUNT(thecalledrecordtitle) AS TheCount
FROM tbl_calledrecords
WHERE thecalledtime BETWEEN #CreateODBCDate(arguments.thestartdate)
# AND #CreateODBCDate(DateAdd('d',1,arguments.theenddate))#
GROUP BY thecallingscript, thecalledrecordtitle
ORDER BY thecallingscript, TheCount DESC, thecalledrecordtitle
</cfquery>
The code for the <cfoutput> tag looks like this:
<cfoutput query="recordcount" group="thecallingscript">
Here were the records called to the page
<strong>#thecallingscript#</strong>:<br>
<table width=600 cellpadding=2 cellspacing=0 border=1>
<cfoutput>
<cfif TheCount gte arguments.minRecords>
<tr>
<td style="font-size:11px;" valign="top" align="left" width=80>#TheCount#</td>
<td style="font-size:11px;"" valign="top" align="left">#thecalledrecordtitle#</td>
</tr>
</cfif>
</cfoutput>
</table><br>
<br>
</cfoutput>
And that results in a display that looks like Table 1.
Note the difference between <cfoutput>'s "group" attribute and "GROUP BY" in the SQL statement. GROUP BY is used with aggregate functions to sort rows into the specified groups before applying an aggregate function to each group.)
What <cfoutput>'s group attribute does is isolate the query column that identifies a group defined by the query's ORDER BY clause, whereupon you can, with an additional <cfoutput> tag nested inside, output the individual rows that have matches to that specified query column. In our example, we output the variable #thecallingscript# and use it as a title for an output of each set of rows creating a match for the different instances of #thecallingscript#.
This use of the "group" attribute can be nested at as many levels as you need. You can have a query that orders rows by several fields, like this:
<cfquery name="thequery" datasource="datasource">
SELECT maincategory, subcategory, subsubcategory, description, theurl
FROM thetable
ORDER BY maincategory, subcategory, subsubcategory, description
</cfquery>
And you can output it by nesting at each group level like Table 1:
<cfoutput query="thequery" group="maincategory">
<ul>
<li>#maincategory#</li>
<cfoutput group="subcategory">
<ul>
<li>#subcategory#</li>
<cfoutput group="subsubcategory">
<ul>
<li>#subsubcategory#</li>
<cfoutput>
<ul>
<li><a href="theurl">#description#</a></li>
</ul>
</cfouput>
</ul>
</cfoutput>
</ul>
</cfoutput>
</ul>
</ul>
</cfoutput>
Note that the source query is identified only in the outermost top-level <cfoutput> tag, and that it's not necessary to name the "group" at the innermost level. We'll see in the next example that it's also possible (and very useful) to have more than one <cfoutput> at a given level of the nesting.
What You See Is What You Output, Made to Look the Way You'd Like It To
Because this means of grouping data comes on the output side of the process, the means of styling the display can be made an integral part of the grouping, such as our simple use of unordered list tags in the example above, and is independent of any of the query logic except the ORDER BY clause - complex queries that join two or more tables use aggregate functions. Filters by various parameters are ready for use in the "group" attribute of cfquery.
What's more the various nested levels of grouped output can each have their own display treatment, allowing for some handy integration with CSS or other display code.
A case in point came up recently when one of my clients, a very successful biomedical firm that operates plasma donation centers in a couple of dozen U.S. states, asked for a map application on its Web site that would show its donation centers by state, with detail pages as the site visitor drilled down to specific states and cities.
There are countless examples on this kind of display on various sites around the Web, a large number of which employ HTML <map> and <area> tags to define coordinates of spots on a map that can be linked (Table 2). For users of an application server like ColdFusion, however, this can be made into a dynamic display and the "group" attribute of <cfquery> plays a central role in passing information from the database into CSS that makes the display work.
For the biomedical firm, I started by creating a graphical map of the U.S. and then measured coordinates for the spot on each state where a text abbreviation would sit; these coordinates are stored in a simple table called "tbl_states":
The "tbl_states" table gives me a set of map coordinates to join with my ongoing database of plasma center locations for this query that will get the information needed for the display:
<cfquery name="getmapinfo" datasource="#thedatasource#">
SELECT distinct c.state, c.city, c.status, c.state_full, s.state_ab, s.state_top, s.state_left
FROM tbl_states s
LEFT OUTER JOIN biocenters c
ON s.state_ab = c.state
ORDER BY state_ab, status, city
</cfquery>
In Table 3 note that we get a "state_ab" result for every state, as well as the coordinates needed on our map, but for those states that are only found in "tbl_states" and not in "biocenters" we get a row with no data in the "city" field, a condition that we'll use to trigger a different result than those that do have cities defined.
Our next group level defined by the ORDER BY of the query, and called by the "group" attribute, is "status," and, finally, "city," which we'll call twice, once nested inside of each of the two possible values for "status."
You can see how this information is used by following the program listing for "MainMap.CFM" abridged from the actual site to include only the code we need to illustrate the process used to populate the map display (see Listing 1).
The first item of business once we've called the query is to offset our map coordinates to account for the spot on the page where the containing div will display it. Then we begin a rather detailed grouped output that's used to set styles for the items that will display on the raw map.
Because we used a left outer join in our "getmapinfo" query, at the level of the group="state_ab" the parameter #state_ab# will be a list of the 51 states (including the District of Columbia) whether or not there are join matches. We'll evaluate each of these rows to see whether there's a city defined (because we asked for "SELECT distinct," those cities that have more than one match will be listed only once). The rows with no city defined (meaning there are no plasma centers in that state) are used to create one kind of div, "#state_ab#_set," and those that do have at least one city defined have created for them some additional offset information, the div "#state_ab#abbrev" and the additional "div #state_ab#detail."
That first <cfoutput>, in the <style> section of the <head> block, used only the topmost group of the query, since all we needed to do was list the states and evaluate if, based on whether they had data in their #city# fields, they needed merely the div that will write the abbreviation in place on the map; or, if they did have centers present, the richer pair of divs that we'll use to provide rollover display and links.
Digging in at All Three Levels
In the body of the
page, however, we use a <cfoutput> that employs the "group"
attribute at all three of the levels offered by the query's "ORDER BY"
clause.
Once again, the <cfoutput> begins by being grouped on "state_ab" and we evaluate whether a given #state_ab# has city data associated with it, our tip-off that there's at least one plasma center in that state. For those with no city, we write the div that writes the state abbreviation in white onto the map and we're done with that state.
But for those states with city data, we write a link that will pass the #state_ab# parameter to a page that will show the detailed information, and then after we pull the #state_full# state name to head the rollover div, we have a nested <cfoutput> that is grouped on "status," so we can differentiate between centers that are already open and those in the preparation stages. Here's a case in which we use the next level of the <cfoutput> grouping twice, once for each of our two possible values of "status."
The result is a map that highlights those states that have biocenters in them, which provides those states with a link to a detail page for drilling down and summarizes the centers in the state grouped by open status in a rollover div that shows when the mouse hovers on the link.
Grouped Newspaper Columns
I'll pass along one more example of the use of the "group" attribute that I find particularly useful (Figure 1). There are times that I like to display query results in newspaper-like columns (Table 4).
Because this grouping of the data all happens on the output side, we
can operate the logic that determines how a column will be distributed
at whichever levels of the nested <cfoutputs> that helps us make
an attractive display. In the case of listing the states and cities
with biocenters in them, I wanted the cities to stay grouped with their
states. First I set up the query "GetSoonAndOpenCenters" with the ORDER
BY state_full, city clause, a sub-query that counts the distinct
instances of state_full, and output it as shown in Listing 2.
Note that rather than use the query's rowcount as the trigger for jumping to a new column with the <td> tag, we set up a incrementer based on the "state_full" count and put the evaluation trigger for the column change at the end of each iteration of that outer <cfoutput> group. The result is a reasonably evenly distributed three-column display that keeps the cities grouped in their state.
In all of these examples, the utility of the "group" attribute rests with the fact that it resides on the output side of our data handling, giving us multiple ways of distributing and displaying the results of a given query. This includes adding styling, conditional branches in the various nested output levels, and the association of our choice of styling with each sublevel of the output. Sometimes it reminds me of my dear mom, who used to stress to me in my school years that getting in with the right group was the key to success!.