Spreading SQL And CFQueryParam Tags Across Multiple Function Calls In Lucee CFML 5.2.9.40
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:
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
Hacky is not my question. Have you run this SQL against a query analyzer to see if it performs well or not?
@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:
I get this
EXPLAIN
results:And, if I inline the sub-query:
... I get the following
EXPLAIN
output:Same thing. Though, again, this is my local DB, very little data.
That is awesome. When we do creative SQL we forget to run it against query analyzer.
@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
andhttpParam
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! 💪
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.👍
@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 splitCFQuery
andCFQueryParam
across methods gives this error:So lame!! This technique was super helpful.
Post A Comment — ❤️ I'd Love To Hear From You! ❤️
Post a Comment →