<cfoutput> With Many-To-Many Relationship

I am working on a project for our accounting department. I have a database (MySQL) table with ledger codes. Our company has a few different office locations, and each of these codes can apply to one or more office location. Each office location can have one or more ledger codes that apply. So I have a many-to-many relationship with a bridge table holding the code_id and the location_id. My SQL is as follows:

SELECT gl.`code_id`, gl.`account_code`, gl.`account_type`, gl.`account_desc`, glloc.`location_id`
FROM `gl_codes` as gl
    LEFT JOIN `gl_codes_locations` as glloc
ON gl.`code_id` = glloc.`code_id`
ORDER BY gl.`code_id`, glloc.`location_id`

This results in a table with a separate row for each code_id/location_id pair. I want to display this in a table using cfoutput. I want only one row for each code_id, but I will use a column in each row to mark whether that code applies to a given location_id, like so:

| CodeAccount | CodeType | CodeDescription | Code Location | | | | | 1 | 2 | 3 | 4 | |SomeAcct | SomeCode | Some Desc | X | | X | |

I know that I cannot nest cfoutput tags with multiple query attributes. I've tried some grouping, but I can't seem to get it right. Please help!

Solution

This should get you pretty close. First we need a list of available IDs, so we know how many Location sub-columns we need.

<cfquery name="locationData">
  SELECT location_id FROM gl_codes_locations ORDER BY location_id
</cfquery>
<cfset allLocationIds = ValueList(locationData.location_id)>

Then, inside the table we can build the header and body using this information:

<thead>
    <tr>
      <td>Code ID</td>
      <td>Code Account</td>
      <td>Code Type</td>
      <td>Code Description</td>
      <td colspan="#ListLen(allLocationIds)#">Code Location</td>
    </tr>
    <tr>
      <td></td>
      <td></td>
      <td></td>
      <td></td>
      <cfloop list="#allLocationIds#" index="id">
        <td>#HtmlEditFormat(id)#</td>
      </cfloop>
    </tr>
</thead>
<tbody>
  <cfoutput query="ledgerData" group="code_id">
    <cfset currLocationIds = "">
    <cfoutput>
      <cfset currLocationIds = ListAppend(currLocationIds, location_id)>
    </cfoutput>
    <tr>
      <td>#HtmlEditFormat(code_id)#</td>
      <td>#HtmlEditFormat(account_code)#</td>
      <td>#HtmlEditFormat(account_type)#</td>
      <td>#HtmlEditFormat(account_desc)#</td>
      <cfloop list="#allLocationIds#" index="id">
        <td>#ListFind(currLocationIds, id) gt 0 ? 'X' : ''#</td>
      </cfloop>
    </tr>
  </cfoutput>
</cfoutput>