Search code examples
coldfusioncoldfusion-2016qoq

Using cfloop with queryfilter function

I am new to ColdFusion and trying to use cfloop for the below code:

<cfscript>
    var origRate = 0;
    var toRate = 0;

    rates = myQuery.filter(function (obj) {
          return (obj.code == arguments.origCode || obj.code == 
    arguments.toCode)
            })
</cfscript>

I modified below, the original code and inserted the above new code to avoid the inline sql queries:

<cfquery name="rates" dbtype="query">
        select code, rate
  from myQuery
  where code = <cfqueryparam cfsqltype="cf_sql_varchar" value="#arguments.origCode#" />
     or code = <cfqueryparam cfsqltype="cf_sql_varchar" value="#arguments.toCode#" />
</cfquery>

I tried using cfloop without changing to the previous code as below but it is not working:

<cfloop query="rates">
    <cfscript>
        if (code == arguments.origCode) origRate = rate;
        if (code == arguments.toCode) toRate = rate;
    </cfscript>
</cfloop>

Once the second block of code was inserted by commenting out the first block of code above, it did not load the page. If anyone has an idea, I really appreciate it. Thank you in advance!

Solution

  • There were some missing details about the application and data, so I made a couple of assumptions. It appears that you have a query object that you want to filter and pull rates from for an origCode and a toCode. Without knowing more about your data structure and what you plan to do with it, I can only make some general suggestions. I still maintain that it would be much better to filter in the query, but I understand the limitation. Since you have to filter inside your application, both the bulk of the base data you initially return and the processing to filter those records will negatively impact the performance.

    First thing I did was to set up a fake query object. This is where my first assumption comes into play. I assumed that your code won't be any duplicated in your table, and that the code will have a rate associated with it. 

    myQuery = queryNew(
    "code, rate",
    "integer, integer",
    [
      { "code" : 1 , "rate" : 10 } , 
      { "code" : 2 , "rate" : 15 } , 
      { "code" : 3 , "rate" : 20 } , 
      { "code" : 4 , "rate" : 25 } , 
      { "code" : 5 , "rate" : 30 }
    ]
);

    I would not recommend a Query of Query here, because it's a lot of overhead for something that can be accomplished fairly easily.

    I created a function that you can pass in your origCode and the toCode, and it will return you a structure of the origRate and the toRate. I included some comments in the code, so you will be able to see what I was doing. The bulk of the function is using the filter() closure to filter the query records down. If you are able to filter through SQL, you'll be able to eliminate this block. 

    function returnNewRates( required Numeric origCode, required Numeric toCode ) {
    local.ratesStruct = { "origRate":-1, "toRate":-1 } ;

    // This will be our query. If we _have_ to use an existing query, pass it in and duplicate() it. (Pass by Reference!)
    local.qry = duplicate( myQuery )  ; 
    /////////////
    // Closure to filter the query. This should be done in SQL.
    // https://helpx.adobe.com/coldfusion/cfml-reference/coldfusion-functions/functions-m-r/queryfilter.html
    local.filteredQuery = qry
        .filter( function (obj) {
                return ( obj.code == origCode || obj.code == toCode ) ;
        } ) ;

    // Now assign new rates. NOTE: The query shouldn't return more than 2 rows. We can validate if needed.
    for ( var r IN filteredQuery ) {
        if( r.code == arguments.origCode ) { ratesStruct.origRate = r.rate ; }
        if( r.code == arguments.toCode ) { ratesStruct.toRate = r.rate ; }
    }

    return ratesStruct ;
}

    To assign the origRate and toRate, we first create a ratesStruct return value to hold the structure of the rates. After we filter our query, we just loop through those filtered results and check to see if the code in the row matches with our input variables. Another one of my assumptions was that the database would return no more than two records (one origCode and one toCode, or neither). If it is possible to return more than one row for a code, then the output codes will be overwritten by the last related row in the query. If there are other rows appropriate for sorting, then they can be used and only select the top row for the needed rate. I also defaulted the returned rates to a -1 to signify that no rate was found for the code. That can be changed if needed.

    After that, I just ran a few tests to make sure we didn't get any wonkiness. Code is at https://trycf.com/gist/c3b87ca7c508562fd36f3ba6c73829c7/acf2016?theme=monokai.

    And again, I think this can all probably be done within the database itself. Probably by giving you access to a stored procedure that you can pass origCode and toCode to.