With Many-To-Many Relationship

Question

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!

Please edit your question to show what you tried with grouping and what happened when you tried it. — Dan Bracuk, Jan 14 '21 at 17:28

Tomalak · Accepted Answer · 2021-01-14T17:37:24.080

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>

Michael Buckman · Answer 2 · 2021-01-14T19:44:06.880

Thanks to @Tomalac and his ValueList suggestion, I was able to adapt that to my code and get it working the way I wanted. The sub-column tip is great, and I may implement it in the future, but for now we are dealing with a fixed number of locations.

For reference, the relevant completed code is as follows. I have edited the location names for privacy reasons.

<table class="table table-striped table-bordered">
    <thead class="bg-nav text-white">
        <tr>
            <th scope="col" rowspan="2" class="align-middle">Code</th>
            <th scope="col" rowspan="2" class="align-middle">Type</th>
            <th scope="col" rowspan="2" class="align-middle">Description</th>
            <th scope="col" colspan="4" class="text-center">Applies To</th>
            <th scope="col" rowspan="2" class="text-center align-middle">Edit</th>
            <th scope="col" rowspan="2" class="text-center align-middle">Delete</th>
        </tr>
        <tr>
            <th scope="col">Chicago</th>
            <th scope="col">Detroit</th>
            <th scope="col">LA</th>
            <th scope="col">New York</th>
        </tr>
    </thead>
    <tbody>
        <cfoutput query="codes" group="code_id">
                <tr>
                    <!--- Use function in cfcomponent to grab the location(s) that pertain to the given code_id --->
                    <!--- Dump query results into ValueList --->
<cfset codeLocations = ValueList(createObject("component", "com.modules.glcodes").getCodeLocations("query", codes.code_id).location_id)>
                    <td>#account_code#</td>
                    <td>#account_type#</td>
                    <td>#account_desc#</td>
                    <td><cfif ListLen(codeLocations) GT 0 AND (ListContains(codeLocations, "3") GT 0)>X</cfif></td>
                    <td><cfif ListLen(codeLocations) GT 0 AND (ListContains(codeLocations, "2") GT 0)>X</cfif></td>
                    <td><cfif ListLen(codeLocations) GT 0 AND (ListContains(codeLocations, "4") GT 0)>X</cfif></td>
                    <td><cfif ListLen(codeLocations) GT 0 AND (ListContains(codeLocations, "1") GT 0)>X</cfif></td>
                    <td>Edit</td>
                    <td>Delete</td>
            </tr>
        </cfoutput>
    </tbody>
</table>

If you can at all avoid it, don't hit the database server once per table row. Create a query that contains all the data for the entire table. — Tomalak, Jan 14 '21 at 20:14
@Tomalak that's a really good point that I hadn't thought of. Would it be better to do a "master" query and then a query of queries to create the ValueList? — Michael Buckman, Jan 14 '21 at 20:30
Anything that avoids hitting the database will improve page performance. You're grouping the `codes` query by `code_id` already, so with an inner `` like I did you should be getting the IDs you need. — Tomalak, Jan 14 '21 at 20:54
Thanks again @Tomalak! I have modified my code to include the inner `cfoutput>`. I'm still fairly new to CF development, and still wrapping my head around using nested `` tags. — Michael Buckman, Jan 15 '21 at 15:47
`` cannot be nested unless grouped. When grouped, the inner `` runs once per item in the group, while the outer runs once per group. It works like the "group/group header/group detail/group footer" mechanics in Crystal Reports, if you know that. You can set up multiple levels of grouping this way, but you always need to have your group fields pre-sorted, i.e. ColdFusion will start a new group when the value of the group field changes. — Tomalak, Jan 15 '21 at 17:04
Also, use ListFind() / ListFindNoCase() - not ListContains. The latter searches for a substring, instead of a *whole* value. https://trycf.com/gist/27e4d8d276aed791c128986a56ba30c5/acf2018?theme=monokai — SOS, Jan 15 '21 at 21:47

With Many-To-Many Relationship

2 Answers2