Nested grouping with cfouput
ColdFusion
It seems that most of the blogs I read are dealing with a lot of the newer advanced features of ColdFusion MX7. It is easy to forget that there are tons of people that are new to the language that need info on not-so-new features. Someone on the DFWCFUG list asked the following question this week.
I have a query that is joining several tables. I cannot get the group
feature to work like I need to in my <cfoutput>. For example, the query
returns the following results
Tour 1 - Site A - Type A
Tour 1 - Site A - Type B
Tour 1 - Site B - Type A
Tour 1 - Site B - Type B
Tour 1 - Site C - Type A
Tour 1 - Site C - Type B
I would like to group by both site and type? The group="site" works but not
group="type". What am I doing wrong?
My answer follows:
Using this hand-built query, I think this does what you are looking for. I altered the 6th record so that both were under the same type to prove grouping.
I have a query that is joining several tables. I cannot get the group
feature to work like I need to in my <cfoutput>. For example, the query
returns the following results
Tour 1 - Site A - Type A
Tour 1 - Site A - Type B
Tour 1 - Site B - Type A
Tour 1 - Site B - Type B
Tour 1 - Site C - Type A
Tour 1 - Site C - Type B
I would like to group by both site and type? The group="site" works but not
group="type". What am I doing wrong?
My answer follows:
Using this hand-built query, I think this does what you are looking for. I altered the 6th record so that both were under the same type to prove grouping.
<cfscript>
qResult = querynew("tour,site,type");
queryAddRow(qResult, 6);
querySetCell(qResult, "tour", "Tour 1", 1);
querySetCell(qResult, "site", "Site A", 1);
querySetCell(qResult, "type", "Type A", 1);
querySetCell(qResult, "tour", "Tour 1", 2);
querySetCell(qResult, "site", "Site A", 2);
querySetCell(qResult, "type", "Type C", 2);
querySetCell(qResult, "tour", "Tour 1", 3);
querySetCell(qResult, "site", "Site B", 3);
querySetCell(qResult, "type", "Type A", 3);
querySetCell(qResult, "tour", "Tour 1", 4);
querySetCell(qResult, "site", "Site B", 4);
querySetCell(qResult, "type", "Type B", 4);
querySetCell(qResult, "tour", "Tour 1", 5);
querySetCell(qResult, "site", "Site C", 5);
querySetCell(qResult, "type", "Type A", 5);
querySetCell(qResult, "tour", "Tour 1", 6);
querySetCell(qResult, "site", "Site C", 6);
querySetCell(qResult, "type", "Type A", 6);
</cfscript>
<cfoutput query="qResult" group="site">
<strong>#site#</strong><br/>
<cfoutput group="type">
#type#<br/>
<cfoutput>
<!--- list all tours under type --->
#tour#<br/>
</cfoutput>
</cfoutput>
</cfoutput>
qResult = querynew("tour,site,type");
queryAddRow(qResult, 6);
querySetCell(qResult, "tour", "Tour 1", 1);
querySetCell(qResult, "site", "Site A", 1);
querySetCell(qResult, "type", "Type A", 1);
querySetCell(qResult, "tour", "Tour 1", 2);
querySetCell(qResult, "site", "Site A", 2);
querySetCell(qResult, "type", "Type C", 2);
querySetCell(qResult, "tour", "Tour 1", 3);
querySetCell(qResult, "site", "Site B", 3);
querySetCell(qResult, "type", "Type A", 3);
querySetCell(qResult, "tour", "Tour 1", 4);
querySetCell(qResult, "site", "Site B", 4);
querySetCell(qResult, "type", "Type B", 4);
querySetCell(qResult, "tour", "Tour 1", 5);
querySetCell(qResult, "site", "Site C", 5);
querySetCell(qResult, "type", "Type A", 5);
querySetCell(qResult, "tour", "Tour 1", 6);
querySetCell(qResult, "site", "Site C", 6);
querySetCell(qResult, "type", "Type A", 6);
</cfscript>
<cfoutput query="qResult" group="site">
<strong>#site#</strong><br/>
<cfoutput group="type">
#type#<br/>
<cfoutput>
<!--- list all tours under type --->
#tour#<br/>
</cfoutput>
</cfoutput>
</cfoutput>
Aaron Lynch said:
Its also helpful to remember that ORDER BY in the query must exactly match the grouping levels in the loop.
AJ
AJ
Loading....