A collection filter has all the same properties as a standard filter object. The only difference is that instead of using a simple value (a date, string or number) as the content, it uses a collection (structure). Also as a result, it has no "column" property. In this respect a collection filter works like an insert or update statement, mapping any keys in the structure to matching columns in the table.
The collection filter has one additional property named "like" which is one of "begin", "end" or "contains" (default) indicating whether character columns in the generated SQL should include wild-cards (%) at both ends of the string or at only one end. Using "begin" will result in a query where the results begin with the content value, using "end" will result in a query where the results end with the content value and using "contains" will result in a query where the content exists anywhere in the column.
The collection filter also performs some input scrubbing. Any columns not found in the table are ignored, which should prevent SQL-injection attacks via this filter. Additionally any keys in the structure containing empty strings are also ignored, so that a user's results won't be limited by any fields they leave blank in a search form.
<cfscript> // get the datasource ds = request.DataFaucet.getDatasource(); // create a select statement stmt = ds.getSelect("*","tblEmployee emp"); // filter by employee first name, last name and/or middle initial stmt.collectionFilter(content=form,comparison="like",like="begin"); // get the results query = stmt.execute(); </cfscript>
Assuming the user had entered firstname="j" and lastname="Danvers" in the form, the above code will generate SQL syntax like this:
SELECT * FROM [DataWarehouse].[DBO].[tblEmployee] [emp] WHERE UPPER([emp].[firstname]) LIKE <cfqueryparam value="J%" cfsqltype="cf_sql_varchar" /> AND UPPER([emp].[lastname]) LIKE <cfqueryparam value="DANVERS%" cfsqltype="cf_sql_varchar" />
If you don't need it to be that specific, it defaults to comparing with "LIKE=CONTAINS" and non-character columns are compared with equality (=). So for most purposes, this would suffice:
<cfset stmt.collectionFilter(form) />
Further using the default "contains" value for the like property, date and numeric columns can be filtered with upper and lower bounds by introducing collection elements with "start/end" or "min/max" in the column name.
For exmaple if the table contains a colum named "price", you can additionally filter the query to results with a minimum price using either a "minprice" or "pricemin" input. The upper limit on the same column can be set using either a "maxprice" or "pricemax" input.
Similarly dates can be filtered with "start" and "end". So with the column "saledate", you can limit the results to sales after a given date with a "startsaledate" or "saledatestart" input. You can also limit the results to sales before a given date with an "endsaledate" or "saledateend" input.
Each of thse inputs can be provided in pairs or singularly. What this means is that you aren't required to include "pricemin" simply because "pricemax" is supplied. Either pricemin or pricemax could be used by itself if desired.