时间戳

首页 > 解决方案 > 如何使用 ColdFusion 2018/Lucee 5.x 创建网格输出以显示来自两个查询的数据

coldfusion - 如何使用 ColdFusion 2018/Lucee 5.x 创建网格输出以显示来自两个查询的数据

问题描述

我有一个权限表、一个角色表和一个映射表。我想为网站管理员创建一个 GUI，以便能够添加新角色并为该角色分配权限。目前它是在数据库中完成的，因此会受到用户错误的影响。

为简单起见，假设我们有四个角色，SuperAdmin、Admin、Manager 和 User。权限是创建、读取、更新、删除。在下图中，SuperAdmin 和 Admin 与 Manager 和 User 匹配（用户错误的产物以及为什么我想要创建 GUI 但我离题了）。

在此处输入图像描述

目前，我的输出为每个角色显示一行，而理想情况下，角色只会显示一次，并且会为相应的权限勾选相应的复选框，类似于以下内容：

在此处输入图像描述

这是我拥有的 CF 代码，我不知道如何处理它来做我想做的事。

<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>

使用上面的代码，这会产生以下输出，这是我能够获得的最接近所需输出的输出。显示了正确的权限，但未选中复选框，并且某些权限未显示。

我想我记得几年前在 easycfm 上作为教程或在论坛中阅读过一些关于此的内容。

在此处输入图像描述

标签： coldfusionlucee

解决方案

如果源表很小，一个简单的方法是结合交叉 + 外连接来返回所有可能的值。然后将查询输出“分组”成行。交叉连接有利于产生“所有组合”，但表大小很重要。连接 2 个 1000 行的表，每个表产生 100 万行！所以尺寸是一个重要的考虑因素。

<!--- 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>

另一种选择是使用某种数据透视表。在数据方面，它们比第一个选项更紧凑。虽然动态内容不太灵活。我不熟悉 MariaDB 的数据透视表实现，但有一个老case技巧应该适用于大多数数据库：

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

推荐阅读