Introducing CF_DMQuery

Posted At : October 1, 2010 10:00 AM | Posted By : Steve
Related Categories: CF_DMQuery,DataMgr

One of my favorite things about ColdFusion compared to other languages is the CFQUERY tag. It is just a real thing of beauty. I rarely use it any more, however, because I use DataMgr for my every day CRUD work (actually I use Manager.cfc, but that is another story). I rarely have to write any SQL code any more.

Rarely isn't the same as never, though. I have actually found a spectrum of cases for which I need to write custom SQL. First is the case where I need something more complicated than what DataMgr is built to handle and I don't need any of the functionality that DataMgr provides. In this case, I can easily move to using CFQUERY and I do so happily.

On the of the end of the spectrum is when I want to use DataMgr, but I need to define a field in a way DataMgr cannot handle with existing relation fields. In this case I use a custom relation field and I use SQL to define the field.

Slightly more complicated than that is a situation where I need to use some SQL in a query that DataMgr is performing, but the query itself is still predominantly a DataMgr query. In that case, I use the AdvSQL argument to inject SQL into DataMgr.

There is, however, another case that gets a bit more complicated. Sometimes, I have a query that is sufficiently complicated that using AdvSQL starts to make me miss CFQUERY but that I want to take advantage of either the ease of DataMgr or one or more relation fields that are defined in it. In these cases, I can use CF_DMQUERY.

For example, in a shopping cart program of mine I wanted a report of "completed" orders but the criteria for completion is in a relation field and I didn't want to rewrite the logic. So, I wrote the following code instead:

<cfset sWhere = {isCompleted=true}>

<cf_DMQuery name="qTrends" DataMgr="#variables.DataMgr#">
SELECT      year(DatePlaced) AS orderYear,
         month(DatePlaced) AS orderMonth,
         '' AS OrderMonthYear,
         count(*) AS NumOrders,
         sum(OrderTotal) AS OrdersTotal
FROM      #variables.table#
WHERE      1 = 1
<cf_DMSQL method="getWhereSQL" tablename="#variables.table#" data="#sWhere#">
GROUP BY   year(DatePlaced), month(DatePlaced)
ORDER BY   year(DatePlaced), month(DatePlaced)
</cf_DMQuery>

That code returns a recordset named "qTrends" using just the SQL that it looks like it uses, filtering by the relationfield "isCompleted" and I don't have to know anything about how isCompleted is calculated.

The DM_SQL portion can be called anywhere within the query or can be called outside of a CF_DMQuery tag if you just want to return a SQL array (perhaps to manually pass into the runSQLArray method later).

The CF_DMQuery tag can also take a CF_DMParam tag which is just the same as a CFQUERYPARAM tag except for CF_DMQUERY (or CF_SQL).

Just like DataMgr itself, CF_DMQuery is open source and free for any use.