How do I create grid output to display data from two queries using ColdFusion 2018/Lucee 5.x

Question

I have a table of permissions, a table of roles, and a mapping table. I'd like to create a GUI for a website admin to be able to add new roles and assign permissions to that role. Currently it's done at the database as such subject to user error.

For sake of simplicity, let's say we have four roles, SuperAdmin, Admin, Manager, and User. Permissions are Create, Read, Update, Delete. In the diagram below SuperAdmin and Admin match as do Manager and User (product of a user error and further why I'm wanting to create the GUI but I digress).

Currently my output displays a row for each Role whereas ideally the Role would be only displayed once and the appropriate checkbox would be ticked for the corresponding Permission similar to the following:

Here is the CF code I have and I don't know how to approach this to do what I want.

<cfquery name="securityGroups" datasource="#application.support#">
Select
    r.roleLabel,
    r.roleID,
    p.permName,
    p.permID
From
    npermissions p Inner Join
    nrole_npermission_map rp On rp.permissionID = p.permID Inner Join
    nroles r On rp.roleID = r.roleID
</cfquery>

<cfquery name="securityPermissions" datasource="#application.support#">
Select
    p.permName,
    p.permID
From
    npermissions p
</cfquery>

<table id="datatables" class="datatables">
    <thead>
        <tr>
            <th>Role</th>
            <cfoutput query="securityPermissions">
                <th>#securityPermissions.permName#</th>
            </cfoutput>
        </tr>
    </thead>
    <tbody>
        <cfoutput query="securityGroups" group="roleID">
            <tr>
                <td>#securityGroups.roleLabel#</td>
                <cfoutput>
                    <td>
                        <input type="checkbox" name="permID" value="#securityPermissions.permID#" <cfif securityGroups.permID eq securityPermissions.permID>checked</cfif> >#securityPermissions.permname# (#securityGroups.permID# eq #securityPermissions.permID#)
                    </td>
                </cfoutput>
            </tr>
        </cfoutput>
    </tbody>
</table>

With the code above, this produces the following output which is the closest I've been able to get to my desired output. The right permissions are displayed but the checkboxes are not checked and of some permissions are not being displayed.

I thought I remember reading something about this years ago on easycfm as either a tutorial or in the forums.

Answer 1

If the source tables are small, a simple approach is combining a cross + outer join to return all possible values. Then "group" the query output into rows. Cross joins are good for producing "all combinations", but table size matters. Joining 2 tables of 1000 rows each produces 1 million rows! So size is an important consideration.

<!--- Returns ALL available roles and permissions --->
<cfquery name="qGridData">
   SELECT r.roleID
        , r.RoleLabel
        , p.permName 
        , p.permID
        , ISNULL(m.permissionID, 0) AS AssignedPermID
   FROM nRoles r 
          CROSS JOIN nPermissions p 
          LEFT JOIN nRole_nPermission_Map m 
          ON m.permissionID = p.permID
          AND m.roleID = r.RoleID
   ORDER BY r.RoleLabel, p.PermName       
</cfquery>

<cfquery name="qGridLabels">
   SELECT p.permName 
   FROM   nPermissions p 
   ORDER BY p.PermName        
</cfquery>
 
<table>
   <cfoutput query="qGridData">
     <th>#permName#</th>
   </cfoutput>
</tr>
<cfoutput query="qGridData" group="roleLabel">
   <tr><td>#roleLabel# (id=#roleID#)</td>
       <input type="hidden" name="roleID" value="#roleID#">
       <cfoutput>
         <td><input type="checkbox" name="roleID_permissions_#roleID#" value="#permID#" <cfif AssignedPermID>checked</cfif>> </td>
       </cfoutput>
    </tr>
</cfoutput>
</table>

Another option is using some sort of pivot table. Data-wise, they are more compact than the first option. Though less flexible with dynamic content. I'm not familiar with MariaDB's implementation of pivot tables, but there's an old case trick that should work with most any database:

SELECT r.RoleID
        , r.RoleLabel
        , MAX(CASE WHEN p.PermName = 'Read' THEN m.PermissionID ELSE 0 END) AS ReadAllowed 
        , MAX(CASE WHEN p.PermName = 'Read' THEN p.PermID ELSE 0 END) AS ReadID 
        , MAX(CASE WHEN p.PermName = 'Create' THEN m.PermissionID ELSE 0 END) AS CreateAllowed 
        , MAX(CASE WHEN p.PermName = 'Create' THEN p.PermID ELSE 0 END) AS CreateID 
        , MAX(CASE WHEN p.PermName = 'Delete' THEN m.PermissionID ELSE 0 END) AS DeleteAllowed 
        , MAX(CASE WHEN p.PermName = 'Delete' THEN p.PermID ELSE 0 END) AS DeleteID 
        , MAX(CASE WHEN p.PermName = 'Update' THEN m.PermissionID ELSE 0 END) AS UpdateAllowed 
        , MAX(CASE WHEN p.PermName = 'Update' THEN p.PermID ELSE 0 END) AS UpdateID 
FROM nRoles r 
          CROSS JOIN nPermissions p 
          LEFT JOIN nRole_nPermission_Map m 
              ON m.permissionID = p.permID
              AND m.roleID = r.RoleID
GROUP BY 
        r.RoleID
        , r.RoleLabel
ORDER BY r.RoleLabel