Parameter
Last updated on
16 May 2021
|
Also applies to ColdFusion
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
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
Syntax
queryFilter(query, function(row [, currentRow] [, query] ){} [, parallel] [, maxThreadCount])
Parameters
|
|
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>