Behind the scenes, in all gateway objects Reactor actually uses a "Query" object to abstract queries.
For example, each gateway object has a method called getByFields(). This method has optional arguments for each field in the table. As you pass in values, Reactor actually creates a Query object, which represents the query you want to run. Each field you pass into the getByFields() method is added to the "where" portion of the Query object.
Once completely configured, that query object is passed off to a getByQuery() method which exists on the AbstractGateway object which we briefly mentioned earlier in this document. That getByQuery() method translates the Query object into a parameterized query, executes it, and returns the results.
There's no reason you can't use this same mechanism to get the total a customer spent. I'll show you how to rewrite the CustomerRecord's getTotalSpent() method using a Query object, though we're not going to get deeply into the details of Query objects at this point.
<cffunction name="getTotalSpent" access="public"
output="false" returntype="numeric">
<cfset
var InvoiceGateway = _getReactorFactory().createGateway("Invoice")
/>
<cfset
var Query = InvoiceGateway.createQuery() />
<cfset
var products = 0 />
<!---
let's filter this to only this customer's invoices --->
<cfset
Query.getWhere().isEqual("Invoice", "customerId",
getCustomerId()) />
<!---
let's join the invoice table to the invoiceproduct and product tables
--->
<cfset
Query.join("Invoice", "InvoiceProduct", "InvoiceProduct").join("InvoiceProduct",
"Product", "Product") />
<!---
let's only return the price field --->
<cfset
Query.returnObjectField("Product", "price") />
<!---
let's run the query --->
<cfset
products = InvoiceGateway.getByQuery(Query) />
<!---
now let's get a value list and add up the results --->
<cfreturn
ArraySum(ListToArray(ValueList(products.price))) />
</cffunction>
You might be wondering why you would use this technique over simply writing the query. It seems, perhaps, a little less efficient than actually running the query. Also, you're doing the aggregate calculation to add up all of the prices in ColdFusion and not on the SQL server.
For the most part you're right. However, there's one big advantage that makes this worth considering:
It's portable!
Let’s say that your application needs to support both MySQL and MSSQL (and, maybe another few DBMS). The Object Oriented query will automatically work in both systems without modification. However, if you were to actually write a query you might need to rewrite the query to make it work on all platforms.
Because of this advantage, it's suggested that OO queries not be placed in the DBMS specific customizable objects. Instead, they should be placed in the customizable objects. (CustomerRecord.cfc instead of CustomerRecordmssql.cfc).
Do you want to have to tweak the query we wrote earlier to run on each DBMS? Maybe… Maybe not… It depends on your application.
But, at the same time, if you could simply write the method one time in the database agnostic file and know that it would work, you might choose to save the time.
There's a lot of power behind Query objects. I suggest reading over the Object Oriented Queries section.