Spreading SQL And CFQueryParam Tags Across Multiple Function Calls In Lucee CFML 220.127.116.11
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 18.104.22.168.
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
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
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 22.214.171.124.
Want to use code from this post? Check out the license.
Hacky is not my question. Have you run this SQL against a query analyzer to see if it performs well or not?
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
EXPLAINon it to see what goes through.
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
And, if I inline the sub-query:
... I get the following
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.
That's what production is for ;) ... just kidding - you're exactly right.
EXPLAINis your best friend for this kind of stuff.
It looks like this technique works generally with tags in Lucee CFML. I just tried the same technique with the
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.👍
Boo ya! Glad to be of service 😊
Post A Comment — ❤️ I'd Love To Hear From You! ❤️
❤️ ❤️ Thank You For Your Comment ❤️ ❤️
In order to keep this website fun, safe, and full of those sweet, sweet feelings, your comment has been submitted for approval.