Skip to main content
Ben Nadel
On User Experience (UX) Design, JavaScript, ColdFusion, Node.js, Life, and Love.

CFQueryParam Value Attribute Seamlessly Handles Arrays In Lucee CFML 5.3.7.47

By Ben Nadel on
Tags: ColdFusion, SQL

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!



Reader Comments

What has two thumbs and hopes you leave a comment? This Guy! (Ben Nadel).

Post A Comment

You — Get Out Of My Dreams, Get Into My Blog
Live in the Now
Oops!
Comment Etiquette: Please do not post spam. Please keep the comments on-topic. Please do not post unrelated questions or large chunks of code. And, above all, please be nice to each other - we're trying to have a good conversation here.