存储用户帐户权限的最佳方法?

这可以在SQL本身中完成,这可能比在代码中操纵数据更具性能。

数据的一个问题是 State , City 和 Building 需要先取消对列的引用,然后才能将这些列转换为逗号分隔的列表。

由于您使用的是SQL 2008,因此可以访问所需的功能。

查询是: http://sqlfiddle.com/#!18/0f4f7/1

; WITH cte AS (
  SELECT
      AccountID, AccessType, AccessLevel
      , CASE AccessLevel
          WHEN 'S' THEN State
          WHEN 'C' THEN City
          WHEN 'B' THEN Building
        END AS Permissions
  FROM Permissions
  WHERE AccountID = 
    <cfqueryparam cfsqltype="cf_sql_integer" value="#session.AccountID#"> 
    /* Dynamic variable here */
)
SELECT DISTINCT AccountID, AccessType, AccessLevel
  , CASE 
      WHEN AccessLevel = 'S' THEN Permissions 
      ELSE LEFT(ca.pl, COALESCE(LEN(ca.pl),0)-1)
    END AS PermissionList
FROM cte
CROSS APPLY (
  SELECT p.Permissions + ', '
  FROM cte p
  WHERE p.AccountID = cte.AccountID
    AND p.AccessType = cte.AccessType
    AND p.AccessLevel = cte.AccessLevel
  FOR XML PATH('')
) ca (pl)     ;

我从CTE开始构建 Permissions 基于 AccessLevel . 如果可以将其放在SQL视图中,则可以忽略 WHERE 这里的语句,调用视图时调用它。如果你能把一个视图放到你的数据库中,那它就是我的首选。

在我有了CTE之后,我只需要选择基列( AccountID , AccessType 和 访问级 然后我 CROSS APPLY 以逗号分隔的 权限 . 我用 FOR XML PATH('') 建立逗号分隔的列表。

如果可以将其转换为视图,那么

<cfquery name="qryUserPerm" datasource="#Application.dsn#">
    SELECT AccessType, AccessLevel, PermissionList
    FROM myPermissionsView
    WHERE AccountID = <cfqueryparam cfsqltype="cf_sql_integer" value="#trim(session.AccountID)#">
</cfquery>

如果没有,则必须尝试在 cfquery 标签。

这将返回一个数据集,比如:

| AccessType | AccessLevel |         PermissionList |
|------------|-------------|------------------------|
|          F |           B | 8010, 5412, 6103, 3106 |

您只有一个结果需要处理,不需要循环。

=========================================================

如果你想进入代码路由,我还是建议你尝试使用 cfscript 构建结构。但是,如果您可以拥有多个访问级别,那么您的结果可能不是您认为应该的结果。你得加倍检查你的数据。

  local.permissionType = q2.AccessType ;
  local.permissionLevel = q2.AccessLevel ;

  switch( q2.AccessLevel ) {
    case "S" :  local.permissionList = q2.State ;
      break ;
    case "C" :  local.permissionList = ListRemoveDuplicates(ValueList(q2.City)) ;
      break ;
    case "B" :  local.permissionList = ListRemoveDuplicates(ValueList(q2.Building)) ;
      break ;
  }

https://trycf.com/gist/e811ec86f0d5a52fd9ce703f897cb5aa/acf2016?theme=monokai

您可以使用 CASE 根据访问级别将所有内容合并到单个列中。

SELECT AccessType
        , AccessLevel
        , CASE AccessLevel
            WHEN 'C' THEN City
            WHEN 'B' THEN Building
            WHEN 'S' THEN State
          END AS AccessValue
 FROM   Permissions
 WHERE  AccountID = <cfqueryparam cfsqltype="cf_sql_integer" value="#session.AccountID#">

然后从该列构建您的列表。不需要cfif。

 <cfset local.permissionType = qryUserPerm.AccessType>
 <cfset local.permissionLevel = qryUserPerm.AccessLevel>
 <cfset local.permissionList = valueList(qryUserPerm.AccessValue)>

你也可以建立 CSV list in SQL only 但不确定在这种情况下是否值得这样做,因为它和在CF中构建一样容易。

SELECT TOP 1 AccessType
        , AccessLevel
        , STUFF(( SELECT ','+ l.AccessValue
                  FROM ( SELECT CASE AccessLevel 
                                  WHEN 'C' THEN City 
                                  WHEN 'B' THEN Building 
                                  WHEN 'S' THEN State 
                                END AS AccessValue
                         FROM   Permissions l
                         WHERE  l.AccountID = p.AccountID 
                     ) l
                  GROUP BY l.AccessValue
                  FOR XML PATH('')
                ),1,1,'') AS PermissionsList
 FROM   Permissions p            
 WHERE  AccountID = <cfqueryparam cfsqltype="cf_sql_integer" value="#session.AccountID#">       

无论如何,使用上面的查询将在一行中返回您需要的所有内容:accesstype、accesslevel和csv权限列表。

 <cfset local.permissionType = qryUserPerm.AccessType>
 <cfset local.permissionLevel = qryUserPerm.AccessLevel>
 <cfset local.permissionList = qryUserPerm.PermissionsList>

我想把这个环去掉。我想这会使事情变得简单一点。

<cfset local.permissionType = "">
<cfset local.permissionLevel = "">
<cfset local.permissionList = "">


<cfif qryUserPerm.AccessLevel EQ "S">
     <cfset local.permissionType = qryUserPerm.AccessType>
     <cfset local.permissionLevel = qryUserPerm.AccessLevel>
     <cfset local.permissionList = qryUserPerm.State>
<cfelseif qryUserPerm.AccessLevel EQ "C">
     <cfset local.permissionType = qryUserPerm.AccessType>
     <cfset local.permissionLevel = qryUserPerm.AccessLevel>
     <cfset local.permissionList = ListRemoveDuplicates(ValueList(permissionList,qryUserPerm.City))>
<cfelseif qryUserPerm.AccessLevel EQ "B">
     <cfset local.permissionType = qryUserPerm.AccessType>
     <cfset local.permissionLevel = qryUserPerm.AccessLevel>
     <cfset local.permissionList = ListRemoveDuplicates(ValueList(permissionList,qryUserPerm.Building))>
</cfif>

而且,如果您希望将来比较列表以获得平等,您可能需要使用:

<cfset local.permissionList = ListSort(local.permissionList,"textnocase","asc")>

更新:

<cfscript>

qryUserPerm = queryExecute("
  SELECT AccessType, AccessLevel, State, City, Building 
  FROM Permissions
  WHERE AccountID = :AccountID 
",
{
  AccountID = {value = Trim(session.AccountID), cfsqltype = "cf_sql_integer"}
},
{
  datasource = Application.dsn 
});

local.permissionType = "";
local.permissionLevel = "";
local.permissionList = "";

if(qryUserPerm.AccessLevel EQ "S"){
   local.permissionType = qryUserPerm.AccessType;
   local.permissionLevel = qryUserPerm.AccessLevel;
   local.permissionList = qryUserPerm.State;
}
else if(qryUserPerm.AccessLevel EQ "C"){
   local.permissionType = qryUserPerm.AccessType;
   local.permissionLevel = qryUserPerm.AccessLevel;
   local.permissionList = ListRemoveDuplicates(ValueList(permissionList,qryUserPerm.City));
}
else if(qryUserPerm.AccessLevel EQ "B"){
   local.permissionType = qryUserPerm.AccessType;
   local.permissionLevel = qryUserPerm.AccessLevel;
   local.permissionList = ListRemoveDuplicates(ValueList(permissionList,qryUserPerm.Building));
}

</cfscript>