![]() |
Subscribe to DataMgr |
Visit this group |
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)>
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.