代码之家 › 专栏 › 技术社区 › espresso_coffee

如何比较cfscript中的列值?

cfloop cfquery cfml coldfusion

1

espresso_coffee · 技术社区 · 6 年前

我想循环查询并比较列值。以下是cfml代码的示例:

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

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

上面的代码应该转换成cfscript,我已经了解了这一点,但还不能弄清楚如何访问列值。

<cfscript>
    public string function permissionList(required string AccountID) {
        local.fnResults = "";
        local.permissionList = "";

        try{
            local.qryPermissions = new Query();
            local.qryPermissions.setDatasource("#Application.dsn#");
            local.qryPermissions.setSQL("SELECT AccessType, AccessLevel, State, City, Building FROM Permissions WHERE AccountID = :AccountID");
            local.qryPermissions.addParam(name="AccountID",value="#trim(arguments.AccountID)#",cfsqltype="cf_sql_idstamp");
            local.qryRes = qryPermissions.execute();

            for ( i = 1 ; i <= qryRes.getResult().recordCount ; i++ ) {
                if(qryRes["AccessLevel"][i] EQ "S"){
                    local.permissionList = "";
                }else if(qryRes["AccessLevel"][i] EQ "S"){
                    local.permissionList = ListRemoveDuplicates(ValueList(qryRes.Agency,","));
                }else if(qryRes["AccessLevel"][i] EQ "C"){
                    local.permissionList = ListRemoveDuplicates(ValueList(qryRes.District,","));
                }else if(qryRes["AccessLevel"][i] EQ "B"){
                    local.permissionList = ListRemoveDuplicates(ValueList(qryRes.Building,","));
                }
            }

            local.fnResults = permissionList;
        }catch(any e){
            local.fnResults = e.message;
            //writeOutput(e.message);    
        }

        return fnResults;
    }

    writeOutput(permissionList(AccountID));
</cfscript>

如果有人能帮忙,请告诉我。

2 回复 | 直到 6 年前

1

2

SOS 6 年前

(来自评论…)

问题是 local.qryRes 实际上不包含查询对象。令人困惑的是,调用execute()不会返回查询,但调用execute().getresult()会返回查询。尝试将工作分配更改为:

local.qryRes = qryPermissions.execute();

到:

local.qryRes = qryPermissions.execute().getResult();

其他一些观察结果:

重要的是 地方的 范围所有函数变量,包括循环索引 i . 否则,如果组件存储在共享范围中,可能会得到一些奇怪和不可预测的结果。

虽然我认为不需要循环,但是如果你做循环,考虑更简单的 for..in 语法,而不是索引循环:

for (local.row in local.qryPermissions ) {
    if (local.row.AccessType eq "S") {
        //... code here 
    }
    .... 
}

由于访问字段是如此紧密相关,我可能会让函数返回一个包含所有三个键(accesstype、accesslevel、permissionlist)的结构,而不是有三个单独的函数。
与其使用循环,不如考虑在另一个线程上使用其中一个建议, Best way to store permissions for the user account?

2

Shawn 6 年前

您还可以使用:

local.qryPermissions = queryExecute(
        "SELECT AccessType, AccessLevel, State, City, Building
        FROM Permissions 
        WHERE AccountID = :AccountID" ,
        {AccountID={value="#trim(arguments.AccountID)#", cfsqltype="cf_sql_idstamp"}}  // Or "?" and "[value=xxx,cfsqltype=xxx]"
    ) ;

然后在没有循环的情况下构建您的权限片段:

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

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

另请参阅我关于另一个问题的注释,即可能存在无意的、半相关的数据。