I was asked a question by one of my students in the Advanced ColdFusion 9 class, the other day.
"How does ColdFusion ORM handle things like compound where clauses and aggregate functions".

We need to start out by defining what each is:

A"compound where clause" is a where clause with multiple elements

SELECT *
FROM tbl_table
WHERE (a=b) and (c=d) orx=y

In this case a=b and c=d must be true, otherwise x=y.

An aggregate function is a calculation that is made on several records or cells of data. SUM, AVG, MAX, MIN and COUNT are examples of aggregate functions that are used in spreadsheets and database programs.

A where clause with an "and" is done pretty easily:

<cfset orderDetail = EntityLoad('orderdetails', {OrderID=100,
 ProductID=1}, true)>

where orderdetails is the "table" (a mapped persistant CFC) and the "where" clause is {OrderID=100, ProductID=1}.

If we need to do anything more complex than this then we need to use Hibernate's HQL query language.

from Adobe "ColdFusion lets you use HQL (Hibernate Query Language) to run queries directly on the database. If you are familiar with HQL, you can use it for running complex queries.

In general, use HQL in the following scenarios:

• The query is not specific to a particular object but only to some fields in the object.
• To retrieve some fields of the object without loading the object.
• When you use table joins.
• When you use aggregate functions like min, max, avg, and count.
• To retrieve entities by specifying a filter that needs to use operators other than AND."
  

So how do we begin applying HQL?

ColdFusion ORM has abstracted HQL Calls into: ORMExecuteQuery() with the following syntactical possibiltites:

• ORMExecuteQuery(hql, [params] [,unique])
• ORMExecuteQuery(hql, [,unique] [, queryoptions])
• ORMExecuteQuery(hql, params [,unique] [,queryOptions])
• ORMExecuteQuery (hql, params, boolean unique, Map queryOptions)
• ORMExecuteQuery(hql, [,unique] [, queryoptions])

for a compound where it would look something like this

<cfset filterTable = ORMExecuteQuery("from ARTIST where a
 = b and c = d or x = ?", [40])> 

In this example the question mark represents a dynamic value, 40 is that value passed in as a one dimentional array it could easily be
[#form.value#]. If we were to pass in multiple values they would be evaluated and assigned in order.  

In the case of aggregate functions HQL supports the following:

• avg(...)
• sum(...)
•  min(...)
• max(...)
• count(*)
• count(...)
• count(distinct ...)
• count(all...)

These would be applied in the same manner as the compound where

<cfset aggTable = ORMExecuteQuery( "SELECT COUNT(*)
 FROM tbl_table", {tblID=1})>

So in a nutshell, for complex SQL use HQL