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

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

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

@John D,

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

@All,

It looks like this technique works generally with tags in Lucee CFML. I just tried the same technique with the http and httpParam tags:

www.bennadel.com/blog/4117-spreading-http-and-httpparam-tags-across-multiple-function-calls-in-lucee-cfml-5-3-8-201.htm

In that post, I augment the output HTTP call with HTTP Headers that are being defined inside Function calls farther down in the call-stack. So cool! 💪

@Andrew,

Boo ya! Glad to be of service 😊

So, I was just attempting to use this technique (splitting tags across function calls) in the context of CFHttp / CFHttpParam tags, and it seems that Adobe ColdFusion doesn't work this way. Splitting tags is a Lucee CFML only feature (tested in Adobe ColdFusion 2021 and 2023). Attempting to split CFQuery and CFQueryParam across methods gives this error:

The tag must be nested inside a cfquery tag.

Context validation error for tag cfqueryparam.

So lame!! This technique was super helpful.