Introducing CF_DMQuery

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)

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.

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.