Statement Filters
A filter can be applied to a Select, Update or Delete statement, allowing the operation to be limited to a specific set of target rows. Filter objects are most easily created from the statement object via its filtering methods like filter() and nullFilter().
Example:
<cfscript>
// get the datasource
ds = request.DataFaucet.getDatasource();
// get a select statement for the product table
stmt = ds.getSelect("*","tblProduct");
// filter products by the selected category
stmt.filter("productCategoryID",URL.productCategoryID);
// get the query
query = stmt.execute();
</cfscript>
The filter object has these properties:
| Property | Default | Description |
|---|---|---|
| column | n/a | required - name of the column to filter results |
| include | n/a | and/or - indicates whether filters are cumulative (and) or mutually exclusive (or) - overrides the include property of the statement object if set |
| content | n/a | content to match in the filtered column |
| comparison | LIKE | indicates the manner in which the content of the filter is compared against data in the column - defaults to = for non-character columns - valid values are <, = ,>, =<, =>, <>, IN, NOT IN, NULL, NOT NULL and LIKE |
| delimiters | , | used with comparisons of IN and NOT IN - the content property is treated as a list separated by these characters |
| dynamic | false | boolean - indicates that the content argument is a string of arbitrary SQL - this will not create a CFQUERYPARAM tag when executed |
| reverse | false | boolean - indicates that the filter should be reversed, checking for the column value within the content string |
| caseSensitive | false | indicates that the content of columns should not be made case-insensitive for databases with case-sensitive collation |