DataMgr 2.5 Documentation: Filters
DataMgr Information
|
| Subscribe to DataMgr |
| Visit this group |
Filters
The "data" argument of the getRecords method only tests for equality. If you need any other kind of operator then this argument will not help you. Using filters, you can pass in an array of structures. Each structure has the following keys.- field (required): The name of the field to filter (can be a real field or a relation field).
- value (required): the value by which to filter.
- operator (optional): The operator by which to filter. Uses "=" if nothing else is passed in.
- possible values: =,>,<,>=,<=,LIKE,NOT LIKE,<>,IN,NOT IN
- or: EQUAL,EQ,NEQ,GT,GTE,LT,LTE,IS,IS NOT,NOT
So, to filter for records from an "applications" table where the "AppliedDate" field is between January 1, 2007 and January 16, 2007 you could use the following code:
<cfset aFilters = ArrayNew(1)>
<cfset sFilter = {field="AppliedDate",operator="GTE",value="1/1/2007"}>
<cfset ArrayAppend(aFilters,sFilter)>
<cfset sFilter = {field="AppliedDate",operator="LTE",value="1/16/2007"}>
<cfset ArrayAppend(aFilters,sFilter)>
<cfset qApplicants = Application.DataMgr.getRecords(tablename="applications",filters=aFilters)>In Relation Field Definitions
Filters can also be used inside relation field definitions to reduce the possible records to be returned.
For example, if you wanted a special field to show how many million dollar houses a house seller is representing, you could do the following:
<field ColumnName="NumMillionDollarHomes">
<relation
type="count"
table="homes"
field="HomeID"
join-field="SellerID"
>
<filter field="price" operator="GTE" value="1000000" />
</relation>
</field>This could also be defined by way of the setColumn method:
<cfset aFilters = ArrayNew(1)>
<cfset aFilters[1] = {field="price",operator="GTE",value=1000000}>
<cfset sRelation = {type="count",table="homes",field="HomeID",join_field="SellerID",filter=aFilters}
<cfset DataMgr.setColumn(ColumnName="NumMillionDollarHomes",Relation=sRelation)>If the "table" has a "sold" field, you could also filter to only show how many million dollar homes they have sold.
<field ColumnName="NumMillionDollarHomes">
<relation
type="count"
table="homes"
field="HomeID"
join-field="SellerID"
>
<filter field="price" operator="GTE" value="1000000" />
<filter field="sold" operator="=" value="true" />
</relation>
</field>Note that DataMgr will not attempt to filter by any cf_sql_longvarchar or cf_sql_clob fields.