Phill Nacelli's SQL Tip Is Making My CFQuery Upgrades In Adobe ColdFusion 2021 Easy
As I've started to modernize my blogging platform for Adobe ColdFusion 2021, one of the things that I was dreading was the lack of Lucee CFML's Tag Islands. Tag Islands have really been a game changer for me, allowing me to seamlessly execute the CFQuery tag inside CFScript. I was afraid that I was going to have to keep using Tag-based syntax for my Gateway / Data Access components. But then, I remembered a hot tip from Phill Nacelli on giving dynamic SQL statements a consistent structure. It turns out, Phill's technique is making it bearable for me to use the queryExecute() Function in lieu of the CFQuery inside a Tag Island.
To quickly recap the power of the CFQuery tag, it's tag-based nature allows for the SQL statement to by dynamically generated based on ColdFusion control flow constructs like <cfif>:
<cfquery name="results">
SELECT
l.id,
l.assetID,
l.jobID,
l.status,
l.errorMessage,
l.createdAt,
l.updatedAt,
l.version,
l.type
FROM
layer_sync_log l
WHERE
TRUE
<cfif id>
AND
id = <cfqueryparam value="#id#" sqltype="integer" />
</cfif>
<cfif jobID.len()>
AND
jobID = <cfqueryparam value="#jobID#" sqltype="varchar" />
</cfif>
<cfif assetID>
AND
assetID = <cfqueryparam value="#assetID#" sqltype="integer" />
</cfif>
</cfquery>
Here, we're using the WHERE TRUE condition to setup our dynamic SQL query; then, we're using the <cfif> tag to seamless add conditions to that query. It's so easy! And it's something I've been doing forever in ColdFusion's tag-based syntax.
ASIDE: I believe this is objectively the most amazing syntax for generating a dynamic SQL statement in any language. It collocates the parameter binding with the SQL condition. And, it's absurdly readable and clear as to what is the author is trying to create. This is a hill I am willing to die on!
When switching from tags to CFScript, creating dynamic "strings" of SQL becomes much less elegant (unless you are using Tag Islands in Lucee CFML). And that's where Phill Nacelli's tip comes in. Instead of using the <cfif> tag to conditionally include a filter condition, I can use MySQL's NULL-safe comparison operator to short-circuit conditions that I don't want executed.
To see what I mean, here's the same "dynamic" SQL statement in CFScript using queryExecute():
<cfscript>
public query function getLogsByFilter(
numeric id = 0,
string jobID = "",
numeric assetID = 0
) {
var results = queryExecute(
"
SELECT
l.id,
l.assetID,
l.jobID,
l.status,
l.errorMessage,
l.createdAt,
l.updatedAt,
l.version,
l.type
FROM
layer_sync_log l
WHERE
(
:id <=> NULL
OR
l.id = :id
)
AND
(
:jobID <=> NULL
OR
l.jobID = :jobID
)
AND
(
:assetID <=> NULL
OR
l.assetID = :assetID
)
",
{
id: {
value: id,
cfsqltype: "cf_sql_integer",
null: ! id
},
jobID: {
value: jobID,
cfsqltype: "cf_sql_varchar",
null: ! jobID.len()
},
assetID: {
value: assetID,
cfsqltype: "cf_sql_integer",
null: ! assetID
}
}
);
return( results );
}
</cfscript>
Instead of using the <cfif> tag to conditionally include a SQL condition, I'm always including it; but, I'm setting the value to NULL in order to short-circuit the condition when the given filter shouldn't be included. Essentially, when a filter needs to be omitted, the first part of the relevant AND condition evaluates to:
NULL <=> NULL
And, as a constant that is always TRUE, the MySQL query planner short-circuits and strips the entire condition out of the executed SQL statement. Therefore, only non-NULL values are used to drive the WHERE clause.
Does this read as easily as the Tag Based CFQuery implementation? No - I don't think so. But, it is readable (especially once you recognize the pattern of conditional execution). Ultimately, I would love for Adobe ColdFusion to adopt the Tag Islands feature in Lucee CFML. But, until that happens, Phill's tip on creating consistent SQL statements has been a life saver for me and my ColdFusion modernization adventure!
Want to use code from this post? Check out the license.
Reader Comments
Not seen that null-safe comparison operator before.
Is it any different from saying
:id IS NULL? Because if not, I find that much more readable...To (maybe) answer my own question, I think in this case there's no difference, as you're specifying NULL in the query. But the null-safe operator would be better if one or both of the values could be null or not...
@Seb,
In this case, yeah,
<=>andIS NULLwould be the same (and would possibly be more readable in this case actually). The main value-add of this operator is that it it works with both null and non-null values. But, since my condition is always comparing to null, thenIS NULLwould probably be a better choice.I always prefer to build the SQL string and then execute.
You can use any sort of logic then to build the string.