Ask Ben: Grouping Query Output By Column Value In ColdFusion

<cfscript>

	// Our sample data here contains orders from various stores. We want to iterate over
	// this query so that we can get information about each GROUPING of stores.
	retailers = queryNew(
		data = [
			{ "storeName": "Acme", "orderID": 1 },
			{ "storeName": "Super Store", "orderID": 3 },
			{ "storeName": "Acme", "orderID": 2 },
			{ "storeName": "Hi-Tech Store", "orderID": 999 },
			{ "storeName": "Super Store", "orderID": 4 },
			{ "storeName": "Super Store", "orderID": 5 }
		]
	);

	// ------------------------------------------------------------------------------- //
	// ------------------------------------------------------------------------------- //	

	// Group the query on the store name. This will give us a STRUCT in which each each
	// store name "key" points to an ARRAY of rows associated with that store name.
	retailersIndex = groupQueryBy( retailers, "storeName" );

	// Our index is using an ORDERED STRUCT so that our for-loop here will iterate over
	// the stores in the same order they appeared in the original query.
	for ( storeName in retailersIndex ) {

		// Get all of the orders that were keyed on this store name.
		// --
		// CAUTION: This is slightly different than the native "group" attribute on the
		// query in that we will have all rows with the given key, not just rows that are
		// right next to each other.
		orders = retailersIndex[ storeName ];

		writeOutput( "<h2> #storeName# </h2>" );
		writeOutput( "<h3> #numberFormat( orders.len() )# Total Order(s) </h3>" );

		for ( order in orders ) {

			writeOutput( "- Order ID : #order.orderID# <br />" );

		}

		// I tested the include-base approach locally (it works). But, I'm only using a
		// single file for the demo.
		// --
		// include "./inner-test.cfm";

	}

	// ------------------------------------------------------------------------------- //
	// ------------------------------------------------------------------------------- //

	/**
	* I return an ORDERED STRUCT that groups the given query on the given column name.
	* Each entry in the resultant index contains an array of corresponding rows.
	*/
	public struct function groupQueryBy(
		required query rows,
		required string columnName
		) {

		// NOTE: We're using an ORDERED STRUCT here so that the iteration order of the
		// grouping matches the iteration order of the original query object.
		var index = [:];

		for ( var row in rows ) {

			var groupKey = row[ columnName ];

			// If we've already encountered a row with the same group-key, add the row to
			// the existing aggregate.
			// --
			// CAUTION: This behavior differs slightly from the native "group" attribute
			// in that rows do NOT have to be side-by-side. This approach will aggregate
			// any and all rows with the same key, regardless of where they are in the
			// overall query.
			if ( index.keyExists( groupKey ) ) {

				index[ groupKey ].append( row );

			// If we haven't encountered this group-key yet, create a new aggregate using
			// the current row. All subsequent matches will be added to this aggregate.
			} else {

				index[ groupKey ] = [ row ];

			}

		}

		return( index );

	}

</cfscript>

<cfscript>

	for ( storeName in retailersIndex ) {

		// ... store details ...

		for ( order in retailersIndex[ storeName ] ) {

			// ... order details ...

		}
	}

</cfscript>

## Acme

### 2 Total Order(s)

- Order ID : 1
- Order ID : 2

## Super Store

### 3 Total Order(s)

- Order ID : 3
- Order ID : 4
- Order ID : 5

## Hi-Tech Store

### 1 Total Order(s)

- Order ID : 999