coldfusioncfmlcfquerycfloop

How to compare column values in cfscript?


I would like to loop over query and compare column values. Here is example of CFML code:

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

Code above should be translated to cfscript, I got this far but can't figure it out how to access column values.

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

If anyone can help please let me know.


Solution

  • (From comments ...)

    The issue is local.qryRes doesn't actually contain a query object. Confusingly, calling execute() doesn't return a query, but calling execute().getResult() does. Try changing the assignment from:

    local.qryRes = qryPermissions.execute();
    

    To:

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

    A few other observations:

    1. It is important to local scope ALL function variables, including your loop index i. Otherwise, you may get some bizarre and unpredictable results if the component is stored in a shared scope.

    2. Although I don't think a loop is necessary, if you do loop, consider the simpler for..in syntax, instead of an indexed loop:

      for (local.row in local.qryPermissions ) {
          if (local.row.AccessType eq "S") {
              //... code here 
          }
          .... 
      }
      
    3. Since the access fields are so closely related, I'd probably have the function return a structure containing all three keys (AccessType, AccessLevel, PermissionList) rather than having three separate functions.

    4. Rather than using a loop, consider going with one of the suggestions on your other thread, Best way to store permissions for the user account?