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

I've posted my NCDevCon presentation to SlideSix, you can view it here: http://slidesix.com/view/Hands-on-ColdFusion-Unit-2

I will be speaking at NCDevCon, the weekend of May 22, at the NC State College of Textiles on the main campus of NC State in Raleigh, NC.

I will be conducting a two hour long hands on session, covering ColdFusion Database integration, manipulation and outputting.

And just for fun I'll throw in some Ajax-y goodness.

Details are here: http://www.ncdevcon.com...

and by the way.. this conference is FREE, as in zero cheap, nada, nothing, just register..

The agnostic nature of cfajaxproxy.

I always thought that cfajaxproxy was inexorably tied to the Ajax functions built into ColdFusion.. I recently found out that
I was wrong.

CFAjaxproxy is used to create a javascript proxy of a ColdFusion Component (cfc) for use in the web client. This means any javascript,
not just ColdFusion based AJAX components.

Lets take the AJAX post code from my last post and see how it would work with cfajaxproxy.

This is the original code: It's calling action pages to execute ColdFusion components.


$("#getTOByWeek").live("click", function(){
   $.ajax({
         type:"post",
         url:"webapps/hr/admin/actions/act_adminHR_Handler.cfm",
        data:$("#toByWeek").serialize(),
        cache:"false",
        success: function(){
            $("#content-box").load("webapps/hr/admin/display/dsp_TOList.cfm");
        },
       error: function(){
           alert("data");
       }
   });
   return false;
});

If I use cfajaxproxy it would look something like this, to achieve the same functionality

<cfajaxproxy cfc = "CFC name" jsclassname = "JavaScript proxy class name">

The CFC property instantiates the entire component, using dot notation (com.foo.bar.component), jsclassname is the name that is given to the function
inside javascript:

<cfajaxproxy cfc="hr.cfc.hr_dao" jsclassname="hr_dao">

This gives me a javascript representation of my hr_dao.cfc that can be used in any javascript case.
//instantiate the ColdFusion Function
<cfajaxproxy cfc="hr.cfc.hr_dao" jsclassname="hr_dao">

//capture the click event
<script>
$("#getTOByWeek").click(function(objEvent){
         //call the ColdFusion method directly

         hr_dao.getTimeOffStatus(objEvent);
        //trigger the success function based on return
       return false;
      });
<script>

Terry  Ryan posted a link to a StackOverflow thread asking about the merits of ColdFusion… while the thread itself is closed, I’m going to post my own commentary here.

Many fellow CF devs came out in support of the product, which we should. What surprised me was the incredible lack of knowledge on the part of the naysayers. One in particular, a humorless little Frenchman named Pascal Thivent…