A query within a loop of a list containing multiple datasources produces records from each datasource. I would like to build a unique list of the divisions and count the number of times the division appears as it loops through the datasources.
This is working, but is there a better way using an array or struct?
<cfset divisions = "">
<!--- within query within loop --->
<cfif ListContains(divisions, "#SearchCompanyList.Division#")>
<cfset variables['division#SearchCompanyList.Division#'] = variables['division#SearchCompanyList.Division#'] 1>
<cfelse>
<cfif Len(SearchCompanyList.Division) gt 0>
<cfset divisions = ListAppend(divisions, "#SearchCompanyList.Division#")>
<cfset variables['division#SearchCompanyList.Division#'] = 0>
</cfif>
</cfif>
<!--- outside of query and loop --->
<cfloop list="#divisions#" index="div">
<cfoutput>#div#: #Evaluate('division'&div)#</cfoutput>
</cfloop>
CodePudding user response:
You are doing way too much variable indirection. Get rid of all the variables["#variablename#"]
and the Evaluate()
, all of that is unnecessary. This goes for the rest of your code, I bet you can simplify it all by a lot.
For your main issue, use a struct:
<cfset divisionCount = StructNew('casesensitive')>
<cfloop query="SearchCompanyList">
<cfif StructKeyExists(divisionCount, Division)>
<cfset divisionCount[Division] >
<cfelse>
<cfset divisionCount[Division] = 1>
</cfif>
</cfloop>
<cfoutput>
<cfloop array="#StructKeyArray(divisionCount)#" item="div">
#HTMLEditFormat(div)#: #divisionCount[div]#<br>
</cfloop>
</cfoutput>
StructNew('casesensitive')
is supported from CF2021. If you don't have that version yet, ColdFusion will upper-case the struct keys. Use a second struct to store the original division names, so that you can output them properly:
<cfset divisionCount = StructNew()>
<cfset divisionName = StructNew()>
<cfloop query="SearchCompanyList">
<cfif StructKeyExists(divisionCount, Division)>
<cfset divisionCount[Division] >
<cfelse>
<cfset divisionCount[Division] = 1>
<cfset divisionName[Division] = Division>
</cfif>
</cfloop>
<cfoutput>
<cfloop array="#StructKeyArray(divisionCount)#" item="div">
#HTMLEditFormat(divisionName[div])#: #divisionCount[div]#<br>
</cfloop>
</cfoutput>
For side-by-side counting of multiple columns, a reusable option with a little more advanced CF would be:
<cfscript>
function toGroupsBy(column) {
return function (result, row) {
var value = row[column];
if (result.keyExists(value)) result[value][2] ; else result[value] = [value, 1];
return result;
};
}
</cfscript>
<cfset byDivision = SearchCompanyList.reduce(toGroupsBy('Division'), {})>
<cfset bySalesPerson = SearchCompanyList.reduce(toGroupsBy('SalesPerson'), {})>
<cfdump var="#byDivision#" label="Grouped by Division">
<cfdump var="#bySalesPerson#" label="Grouped by SalesPerson">
<cfoutput>
<cfloop array="#StructKeyArray(byDivision)#" item="div">
#HTMLEditFormat(byDivision[div][1])#: #byDivision[div][2]#<br>
</cfloop>
</cfoutput>
For nested counting, I would actually use ColdFusion's native output grouping mechanism. That ordering the query properly is necessary for this to work. Note the nested <cfoutput>
tags.
<cfquery name="SearchCompanyList" datasource="mydb">
SELECT
Division, SalesPerson
FROM
MyTable
ORDER BY
Division, SalesPerson
</cfquery>
<cfoutput query="SearchCompanyList" group="Division">
<cfset divCount = 0>
<h3>#HTMLEditFormat(Division)#</h3>
<ul>
<cfoutput group="SalesPerson">
<cfset persCount = 0>
<cfoutput>
<cfset persCount >
<cfset divCount >
</cfoutput>
<li>#HTMLEditFormat(SalesPerson)#: #persCount#</li>
</cfoutput>
<li><b>Overall</b>: #divCount#</li>
</ul>
</cfoutput>
CodePudding user response:
This worked for me for a nested structure, expanding on Tomalak's initial answer.
<cfif StructKeyExists(divisionCount, 'segments')>
<cfif StructKeyExists(divisionCount.segments, SearchCompanyList.Division)>
<cfif StructKeyExists(divisionCount.segments[SearchCompanyList.Division], SearchCompanyList.AccountManager)>
<cfset divisionCount.segments[SearchCompanyList.Division][SearchCompanyList.AccountManager] >
<cfelse>
<cfset divisionCount.segments[SearchCompanyList.Division][SearchCompanyList.AccountManager]=1>
</cfif>
<cfelse>
<cfset divisionCount.segments[SearchCompanyList.Division][SearchCompanyList.AccountManager]=1>
</cfif>
<cfelse>
<cfset divisionCount.segments[SearchCompanyList.Division][SearchCompanyList.AccountManager]=1>
</cfif>