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:
<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
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.