Skip to main content
Ben Nadel
On User Experience (UX) Design, JavaScript, ColdFusion, Node.js, Life, and Love.

Phill Nacelli's SQL Tip Is Making My CFQuery Upgrades In Adobe ColdFusion 2021 Easy

By Ben Nadel on
Tags: ColdFusion, SQL

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!



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

Reply to this Comment

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

Reply to this Comment

@Seb,

In this case, yeah, <=> and IS NULL would 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, then IS NULL would probably be a better choice.

Reply to this Comment

I always prefer to build the SQL string and then execute.

sqlText = "select bookid, title, genre from books where bookid = :bookid ";

You can use any sort of logic then to build the string.

myQry.setSQL(sqlText);
myQry.addParam(name="bookid",value="5",CFSQLTYPE="CF_SQL_INT");
qry = myQry.execute(); 
Reply to this Comment

Post A Comment

You — Get Out Of My Dreams, Get Into My Blog
Live in the Now
Oops!
Comment Etiquette: Please do not post spam. Please keep the comments on-topic. Please do not post unrelated questions or large chunks of code. And, above all, please be nice to each other - we're trying to have a good conversation here.