QueryFilter

Description

Calls the provided function for each row of the provided query and removes the row from query if the function returns false.

Returns

Filtered query.

Category

Query functions

History

ColdFusion (2021 release): Introduced the following parameters:

  • parallel
  • maxThreadCount

Also, in this release, QueryFilter will not mutate the original query. To maintain backward compatibility, there is a JVM flag coldfusion.query.filter.mutateinputquery.

Adobe ColdFusion (2018 release): Introduced named parameters.

Adobe ColdFusion (2016 release): Added the function.

See also

QueryEachQuerySortQueryKeyExists

Syntax

queryFilter(query, function(row [, currentRow] [, query] ){} [, parallel] [, maxThreadCount])

Parameters

Parameter

Description

query

(Required) Query to be iterated over.

filter

(Required) Function to be called with each row of the query. Should return a boolean value.

parallel

(Optional) True if you want to enable parallel programming.

maxThreadCount

(Optional) The number of threads the function can execute. The number of threads must be between 1-50. If the value exceeds 50, there is an exception.

Example 1

<cfscript>
    myQuery = queryNew("id,name,amount","Integer,Varchar,Integer", 
                [ 
                        {id=1,name="One",amount=15}, 
                        {id=2,name="Two",amount=18}, 
                        {id=3,name="Three",amount=32},
                        {id=4,name="Four",amount=27},
                        {id=5,name="Five",amount=43},
                        {id=6,name="Six",amount=71}
                ]);
    filteredQuery=QueryFilter(myQuery,function(obj){
       return obj.amount>=30
    })
    writeOutput("The filtered query is:")
    writeDump(filteredQuery)
</cfscript>

Example 2

<cfscript>
               qoptions = {result="myresult", datasource="cfbookclub", fetchclientinfo="yes"};
               sampleQuery = QueryExecute("select * from books order by bookid", [] ,qoptions);
               
               function filterQuery(any Obj){
                              return (Obj.ISSPOTLIGHT == "Y" ? true : false);
               }

               WriteDump(QueryFilter(sampleQuery, filterQuery));
</cfscript>

The script filters the query result to display objects, where IsSpotlight is “Y”. The script returns an array of structs. 

Using member function

<cfscript>
       myResult=QueryExecute("SELECT * FROM EMPLOYEES",[],{datasource="cfdocexamples"});
       // Execute the function to call each row and return the values of Location
       status=myResult.filter(function (city){
             return (city.LOCATION=="Newton" || city.LOCATION=="San Francisco" ? true : false);
                    
       });
       // Display the values that meet the filter requirements
       WriteDump(status);
</cfscript>

Using parallelization

<cfscript> 
    myQuery = queryNew("id,name,amount","Integer,Varchar,Integer",  
                [  
                        {id=1,name="One",amount=15},  
                        {id=2,name="Two",amount=18},  
                        {id=3,name="Three",amount=32}, 
                        {id=4,name="Four",amount=27}, 
                        {id=5,name="Five",amount=43}, 
                        {id=6,name="Six",amount=71} 
                ]); 
    t_start=GetTickCount() 
    QueryFilter(myQuery,function(obj){ 
       return obj.amount>=30 
    },true,20) 
    t_end=GetTickCount() 
    writeOutput("<br>Time taken with 20 threads:" &  t_end-t_start) 
</cfscript>

Using parallelization- Example 2

<cfscript> 
    // Install the package derby before executing this code. cfpm>install derby 
    qoptions = {result="myresult", datasource="cfbookclub", fetchclientinfo="yes"}; 
    sampleQuery = QueryExecute("select * from books order by bookid", [] ,qoptions); 
      
    function filterQuery(any Obj){ 
                   return (Obj.ISSPOTLIGHT == "Y" ? true : false); 
    } 
    t_start=GetTickCount() 
    QueryFilter(query=sampleQuery, callback=filterQuery,parallel=true,maxthreadcount=20) 
    t_end=GetTickCount() 
    writeOutput("<br>Time taken with 20 threads:" &  t_end-t_start) 
</cfscript>

Using parallelization- Example 3

<cfscript> 
    myResult=QueryExecute("SELECT * FROM EMPLOYEES",[],{datasource="cfdocexamples"}); 
    // Execute the function to call each row and return the values of Location 
    // Display the values that meet the filter requirements 
    t_start=GetTickCount() 
    status=myResult.filter(function (city){ 
          return (city.LOCATION=="Newton" || city.LOCATION=="San Francisco" ? true : false); 
                   
    },true,30) 
    t_end=GetTickCount() 
    writeOutput("<br>Time taken with 30 threads:" &  t_end-t_start) 
</cfscript>