CFQueryParam Value Attribute Seamlessly Handles Arrays In Lucee CFML 5.3.7.47
This almost isn't worth blogging since it's clearly stated in the Lucee CFML docs. But, since some people don't make it a habit to read documentation, I wanted to share this fact more widely and with much excitement: the CFQueryParam tag accepts arrays for its value attribute. And, when given an array, the tag will seamless convert the parameter into a list-based parameter. This is just one of the many developer ergonomic niceties that comes with Lucee CFML.
To see this in action, I'm going to execute three CFQuery tags that include a WHERE IN filtering condition. The SQL IN clause accepts a comma-delimited list of values. And, historically, I'd author my CFQueryParam tag to take a String of values and use the list="true" attribute. But, in the following code, you'll notice that each subsequent CFQueryParam tag does less and less work:
<cfscript>
userIDs = [ 1, 2, 3 ];
```
<cfquery name="rows" result="results" datasource="testing">
SELECT
u.id
FROM
user u
WHERE
/*
In this first example, I'm explicitly casting the ARRAY to a LIST and I'm
telling the CFQueryParam that the passed-in value is a list (not just a
value that happens to have commas).
*/
u.id IN ( <cfqueryparam value="#userIDs.toList()#" sqltype="integer" list="true" /> )
</cfquery>
```
echo( "SQL: #results.sql# <br />" );
echo( "Rows: #rows.recordCount# <br />" );
echo( "<br />" );
```
<cfquery name="rows" result="results" datasource="testing">
SELECT
u.id
FROM
user u
WHERE
/*
In this second example, I'm still telling the CFQueryParam that the
passed-in value is a list; but, I'm deferring the Array-to-List
conversion to the CFQueryParam tag itself.
*/
u.id IN ( <cfqueryparam value="#userIDs#" sqltype="integer" list="true" /> )
</cfquery>
```
echo( "SQL: #results.sql# <br />" );
echo( "Rows: #rows.recordCount# <br />" );
echo( "<br />" );
```
<cfquery name="rows" result="results" datasource="testing">
SELECT
u.id
FROM
user u
WHERE
/*
In this final example, I'm not telling the CFQueryParam anything! I'm
just passing in Array and allowing the CFQueryParam tag to handle ALL THE
LISTY stuff internally. By passing in an Array, the CFQueryParam will
automatically add the "list=true" and convert the Array to a List for me!
*/
u.id IN ( <cfqueryparam value="#userIDs#" sqltype="integer" /> )
</cfquery>
```
echo( "SQL: #results.sql# <br />" );
echo( "Rows: #rows.recordCount# <br />" );
echo( "<br />" );
</cfscript>
To pull just the parameterized conditions out on their own, we have this:
u.id IN ( <cfqueryparam value="#userIDs.toList()#" sqltype="integer" list="true" /> )
u.id IN ( <cfqueryparam value="#userIDs#" sqltype="integer" list="true" /> )
u.id IN ( <cfqueryparam value="#userIDs#" sqltype="integer" /> )
As you can see, I'm providing less information with each CFQueryParam tag. The last one simply passes-in an Array and omits the list attribute. And still, when we run this ColdFusion code, we get the following page output:
SQL: SELECT u.id FROM user u WHERE u.id IN ( ?,?,? )
Rows: 3
SQL: SELECT u.id FROM user u WHERE u.id IN ( ?,?,? )
Rows: 3
SQL: SELECT u.id FROM user u WHERE u.id IN ( ?,?,? )
Rows: 3
As you can see from the executed SQL, each CFQueryParam produced a parameterized list of 3 values. And, each CFQuery statement returned the same number of rows.
Of course, if you have a String-based list of values, you can still use the list="true" attribute on the CFQueryParam tag. But, if are already working with an Array of values, there's no need to generate a list just for the purposes of executing the SQL query - the CFQueryParam tag will seamlessly handle that for you. Love me some Lucee CFML!
Want to use code from this post? Check out the license.
Reader Comments
Just a note that there's going to be a breaking change in a future release of Lucee:
https://dev.lucee.org/t/changed-behavior-with-queryparam-and-arrays-in-5-3-9-91/9740
It looks like the stuff I have in my post here is going to remain as-is - the real breaking change would be if you provided an array and used
list="false". I am not sure what the intention of that code would be. But, just a heads-up.