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.

Assuming the tables aren't huge, a simple approach is combining a cross + outer join to return all possible values. Then "group" the query output into rows. https://pastebin.com/Laa9x4uZ — SOS, May 05 '21 at 03:39
@SOS if you will post your pastebin as the solution, I can mark it correct. I only needed to make a minor edit for MySQL (changed the ISNULL to IFNULL), a detail I neglected to provide in my post. Thank you. I never would have come up with that query on my own and I'm looking forward to studying it closer to understand it better. I learned something new from your solution, thank you. — HPWD, May 05 '21 at 13:17
Cross joins are good for producing "all combinations", but table size matters. Joining 2 tables of 1000 rows each produces 1million rows. So size is an important consideration. A `PIVOT` table is another option, if MySQL supports that. Pros, data-wise it's more compact. Cons, not as good with dynamic content. But if the permission names rarely change, that isn't an issue. — SOS, May 05 '21 at 20:54
10.2.31-MariaDB; permissions wouldn't change that often, only when new users join the team or leave the team. It's not a heavily trafficked site (not yet anyway - hopefully it becomes more useful). It's a skunkworks project. — HPWD, May 07 '21 at 20:15
I'm not familiar with MariaDB's [implementation of PIVOT](https://mariadb.com/kb/en/connect-pivot-table-type/), but looks like it would work, There's also an old CASE trick used to [simulate pivot tables](https://stackoverflow.com/questions/62711642/mysql-mariadb-create-a-pivot-table-view) in older db's. — SOS, May 11 '21 at 00:25
@SOS Don't forget to put your pastebin as the answer so I can mark it correct. :) — HPWD, May 11 '21 at 21:48

score 0 · Accepted Answer · answered May 15 '21 at 13:49

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

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

1 Answers1