Skip to main content
Ben Nadel at cf.Objective() 2013 (Bloomington, MN) with: Sunil Mendu
Ben Nadel at cf.Objective() 2013 (Bloomington, MN) with: Sunil Mendu

CFQueryParam Value Attribute Seamlessly Handles Arrays In Lucee CFML 5.3.7.47

By
Published in , Comments (1)

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

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