Using cfloop with queryfilter function

Question

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!

A simple statement of `not working` is too vague. Please provide expected and actual results for this code. — Dan Bracuk, Nov 28 '18 at 14:22
A couple of other notes. Why are you switching from `tags` to `script` for the loop? `for` loops is just as easy as ``. Example, `for (rate in rates) {...}` https://www.petefreitag.com/cheatsheets/coldfusion/cfscript/ Your conditional logic looks wrong to. It should be `rate = (code EQ arguments.origCode) ? origRate : "";` — Cory Fail, Nov 28 '18 at 14:38
What exactly are you trying to do? What is happening between your filtering of `myquery` and your looping through those results? You may not even need to do two different passes through your query results. Can you provide some basic data and some basic expectations of what you want? — Shawn, Nov 28 '18 at 20:08
I have modified the question @Shawn. Hope it is now clear. Before modifying the original code, it was working. — S M, Nov 28 '18 at 20:19
So still, what are you trying to accomplish? If you just need to filter your query, then do it in the query itself. Your original code is good. You don't want to return a query result to just filter it further in code. `filter()` _can_ do the same thing to a query that a `WHERE` clause can do, but SQL is _MUCH_ better at doing that filtering. If you want to use `filter()` on a query, it should really only be necessary if you need to also use the base query (or another filtered version) somewhere else. — Shawn, Nov 28 '18 at 20:28
But prefer with filter rather than sql due to the requirement and cannot use inline sql queries. — S M, Nov 28 '18 at 20:32
Why? If your SQL query isn't giving you the data you need, you might need to fix the query. SQL is a much more appropriate place to do filtering of results. — Shawn, Nov 28 '18 at 20:34
Will your `myQuery` return more than one row for rates? Do you need to loop it? If you do, you'll be overwriting `origRate` and `toRate` with whatever happens to be the last row of the query. — Shawn, Nov 28 '18 at 20:49
Looking at the logic, it appears you are trying to submit an `origCode` and a `toCode` to your query to get back an `origRate` and a `toRate`. Can the value of `code` be duplicated in your table or is that column unique? And can `origCode` and `toCode` be the same value to pull the same `rate`? There seems to be a lot of extra processing that can be refactored out. Can you provide some example data and what you expect to get? — Shawn, Nov 28 '18 at 21:23
I do understand you may be limited from using inline queries. But IMO this should be a major push-back point, because your abilities to get data will be significantly limited, and this will also significantly increase resource usage if you have to pass around a large data set and then filter it in your application. A database isn't just a storage medium for data. If you can't get the data you need, then you might need to talk with your DBA to get a stored procedure that you can use. That will take your actual query out of your channel, but still allow you to filter appropriately. — Shawn, Nov 28 '18 at 21:29

score 1 · Accepted Answer · answered Nov 29 '18 at 00:42

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.

score 0 · Answer 2 · answered Nov 28 '18 at 14:32

0

If you are receiving an error about an invalid construct it is because the version of CF does not support the == operator. For Adobe ColdFusion, until recently the only supported equals operators have been eq, is or various comparison functions depending on the variables and intentions involved.

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

answered Nov 28 '18 at 14:32

Dan Roberts

4,664
3
34
43

Do those conditional statements work with CF? It looks like he's trying to do an itinerary statement but not quite there. – Cory Fail Nov 28 '18 at 14:43
ternary? no, it is just a basic conditional and set. I believe you are thinking of a statement like `origRate = code eq arguments.origCode ? rate : ''` – Dan Roberts Nov 28 '18 at 14:53
Yeah, autocorrect. I've been living in PHP world and didn't realize you could do a conditional set like this with CF. Thanks for confirming. – Cory Fail Nov 28 '18 at 15:09
@DanRoberts that conditional operators is fine. I think the value assigned for the query is not correct. Thanks – S M Nov 28 '18 at 15:31
@SM - It's not clear what you mean by "not correct". Could you please update your question with an example of the actual results - and how they're different than what you expected? – SOS Nov 28 '18 at 17:00
@Ageax, I have modified the question . Hope it is clear now. Before modifying the original code, it was working. That means I have not modified this cfloop block of code and is not issue with it. – S M Nov 28 '18 at 20:20
Not quite :) What do you mean by didn't load the page? Are you getting an error? Wrong output? – SOS Nov 28 '18 at 23:33

Using cfloop with queryfilter function

2 Answers2