Using Filters in Relation Field Definitions

Using Filters in Relation Field Definitions

It recently came to my attention that I haven't yet covered in blogs or documentation one of the most powerful features in DataMgr. I have covered relation fields and filters, but (until now) not how to use a filter in a relation field definition.

(What is DataMgr?)

I was working on a shopping cart recently where I use one table to store orders. Instead of having one data store for carts and another for completed orders, I decided to have one table and the status of an order.

I actually do this with a combination of relation fields, but the only thing that matters for this example is the cartOrders table has a field (actually a relation field) called "isPlaced" that indicates that the order has been completed (rather than just being a cart).

My reporting on number of completed orders must look at this field so that I don't accidentally include carts in my reporting on hours. Fortunately, this is easy to do within DataMgr.

Here is the NumOrders field in my cartCustomers:

<field ColumnName="NumOrders">
   <relation
      type="count"
      table="cartOrders"
      field="OrderID"
      join-field="CustomerID"
   >

      <filter field="isPaid" operator="=" value="1" />
   </relation>
</field>

This filter element applies the filter to the definition of a relation field. Any given relation field can have multiple filters.

This could have also been done in the setColumn() method:

<cfset aFilters = ArrayNew(1)>
<cfset aFilters[1] = {field="isPaid",operator="=",value=1}>
<cfset sRelation = {type="count",table="cartOrders",field="OrderID",join_field="CustomerID",filter=aFilters}
<cfset DataMgr.setColumn(ColumnName="NumOrders",Relation=sRelation)>

With very little effort, DataMgr allows me to use filters as part of my relation field definition.

DataMgr is open source and free for any use. You can see it in action on the DataMgr demonstration or download it from RIAForge.

Related Blog Entries

Comments (Comment Moderation is enabled. Your comment will not appear until approved.)
BlogCFC was created by Raymond Camden. This blog is running version 5.8.001.