Skip to main content
Ben Nadel at RIA Unleashed (Nov. 2009) with: Tom Jordahl
Ben Nadel at RIA Unleashed (Nov. 2009) with: Tom Jordahl

Spreading SQL And CFQueryParam Tags Across Multiple Function Calls In Lucee CFML 5.2.9.40

By on
Tags:

I've been spending a lot of time lately trying to figure out how to optimize the SQL queries required to render a given view. Ideally, the data in question should be aggregated into some sort of materialized view; or, the User Interface (UI) should be redesigned to show less data. But, unfortunately, those aren't options that I have at my disposal. As such, I'm continuing to tweak and experiment. And, one experiment that I wanted to try was whether or not I could split a SQL query and its CFQueryParam tags up across multiple function calls. This would allow me to do things like factor-out and reuse sub-queries in Lucee CFML 5.2.9.40.

Most SQL sub-queries that I write don't require CFQueryParam tags since they generally reference a column in the outer query. However, to see how flexible this approach could be, I wanted to see if my "distributed SQL" could also contain distributed CFQueryParam tags.

In the following query, I'm fetching a user; and, for that user, I'm also fetching the number of project records that they own. Again, the sub-query in this demo could be written to reference an outer column; but, I wanted to see if a CFQueryParam tag would work:

<cfquery name="user">
	SELECT
		u.id,
		u.name,
		u.email,

		<!---
			Imagine that this is a sub-query that we want to reuse across multiple
			queries. I wanted to see if it could include both SQL and CFQueryParams.
		--->
		#getUserProjectCountSQL( 1 )# AS projectCount
	FROM
		user u
	WHERE
		u.id = <cfqueryparam value="1" sqltype="integer" />
	;
</cfquery>

<cfdump var="#user#" />

<!--- ------------------------------------------------------------------------------ --->
<!--- ------------------------------------------------------------------------------ --->

<cffunction name="getUserProjectCountSQL">

	<!--- Define arguments. --->
	<cfargument name="userID" type="numeric" required="true" />

	<!---
		Output the SQL for the project-count sub-query.
		--
		NOTE: Including a CFQueryParam only works if the Function is set to allow for
		OUTPUT. If output is suppressed, this does not work and the Lucee runtime
		complains that the "parameter index is out of range".
	--->
	(
		SELECT
			COUNT( * )
		FROM
			project p
		WHERE
			p.userID = <cfqueryparam value="#userID#" sqltype="integer" />
	)

</cffunction>

As you can see, the parent query is executing the getUserProjectCountSQL() function as part of its SQL statement. This function call outputs additional SQL, include a CFQueryParam tag. And, when we run this Lucee CFML page, we get the following output:

CFQuery content split across multiple function calls, including CFQueryParam tags, in Lucee CFML.

Holy cow! It worked! Though, it only works if the getUserProjectCountSQL() function is set to allow output. Meaning, the function does not use output="false". Setting the output to be suppressed appears to also suppress the parent-child relationship between the CFQuery and CFQueryParam tags and the Lucee CFML runtime ends up complaining:

Parameter index out of range (2 > number of parameters, which is 1).

To be clear, I don't love this approach. At best, it feels "hacky". That said, sometimes when you're working in a brown-field application with limited wiggle room, "hacky" is all you've got. And, in this case, it is pretty cool to see that the CFQuery content, including nested CFQueryParam tags can be spread across multiple function calls in Lucee CFML 5.2.9.40.

Want to use code from this post? Check out the license.

Reader Comments

15,643 Comments

@John,

I assume it would since the SQL is being assembled on the ColdFusion side before it gets to the MySQL server. That said, I can try to run an EXPLAIN on it to see what goes through.

15,643 Comments

@John,

It seems to be the same. Though, realize that this is on my local dev database which is very small in overall size. If I run this:

<cfquery name="user">
	EXPLAIN
	SELECT
		u.id,
		u.name,
		u.email,

		#getUserProjectCountSQL( 1 )# AS projectCount
	FROM
		user u
	WHERE
		u.id = <cfqueryparam value="1" sqltype="integer" />
	;
</cfquery>

<cfoutput>
	<pre>#new QueryPrinter().toString( user )#</pre>
</cfoutput>

I get this EXPLAIN results:

+----+-------------+-------+-------+----------------------------------------+----------------------------------------+---------+-------+------+-------------+
| id | select_type | table | type  | possible_keys                          | key                                    | key_len | ref   | rows | Extra       |
+----+-------------+-------+-------+----------------------------------------+----------------------------------------+---------+-------+------+-------------+
| 1  | PRIMARY     | u     | const | PRIMARY                                | PRIMARY                                | 4       | const | 1    |             |
| 2  | SUBQUERY    | p     | ref   | userID_isSample_isArchived_isOverQuota | userID_isSample_isArchived_isOverQuota | 4       | const | 33   | Using index |
+----+-------------+-------+-------+----------------------------------------+----------------------------------------+---------+-------+------+-------------+

And, if I inline the sub-query:

<cfquery name="user">
	EXPLAIN
	SELECT
		u.id,
		u.name,
		u.email,
		(

			SELECT
				COUNT( * )
			FROM
				project p
			WHERE
				p.userID = <cfqueryparam value="1" sqltype="integer" />

		) AS projectCount
	FROM
		user u
	WHERE
		u.id = <cfqueryparam value="1" sqltype="integer" />
	;
</cfquery>

<cfoutput>
	<pre>#new QueryPrinter().toString( user )#</pre>
</cfoutput>

... I get the following EXPLAIN output:

+----+-------------+-------+-------+----------------------------------------+----------------------------------------+---------+-------+------+-------------+
| id | select_type | table | type  | possible_keys                          | key                                    | key_len | ref   | rows | Extra       |
+----+-------------+-------+-------+----------------------------------------+----------------------------------------+---------+-------+------+-------------+
| 1  | PRIMARY     | u     | const | PRIMARY                                | PRIMARY                                | 4       | const | 1    |             |
| 2  | SUBQUERY    | p     | ref   | userID_isSample_isArchived_isOverQuota | userID_isSample_isArchived_isOverQuota | 4       | const | 33   | Using index |
+----+-------------+-------+-------+----------------------------------------+----------------------------------------+---------+-------+------+-------------+

Same thing. Though, again, this is my local DB, very little data.

15,643 Comments

@John D,

That's what production is for ;) ... just kidding - you're exactly right. EXPLAIN is your best friend for this kind of stuff.

1 Comments

Thanks for this. Just came across it in my search to get around the issue of CFQueryParams outside of a query.

Was using CFSaveContent blocks to make common parts of multiple queries simpler but, of course, hit the issue with parameterised values.

Answer: Use functions instead. Nice.👍

I believe in love. I believe in compassion. I believe in human rights. I believe that we can afford to give more of these gifts to the world around us because it costs us nothing to be decent and kind and understanding. And, I want you to know that when you land on this site, you are accepted for who you are, no matter how you identify, what truths you live, or whatever kind of goofy shit makes you feel alive! Rock on with your bad self!
Ben Nadel