Lucee 5: simpler queryparam syntax for SQL IN conditions
13 July 2018
Here's a hidden gem you may have missed in Lucee 5 (v5.2.4+).
Consider this basic query for a single record with a given ID.
userID = 1;
sql = "SELECT email FROM users WHERE ID = :ID";
params = { ID: { value: userID, sqltype: "integer" } };
result = queryExecute( sql, params );
If you wanted the emails from more then one user you would use an IN
condition with a list of the IDs you want:userIDs = "1,3";
sql = "SELECT email FROM users WHERE ID IN ( :ID )";
params = { ID: { value: userIDs, list: true, sqltype: "integer" } };
result = queryExecute( sql, params );
This works just fine, but the syntax can now be simplified a little:
userIDs = [ 1, 3 ]; //an ARRAY not a list
sql = "SELECT email FROM users WHERE ID IN ( :ID )";
params = { ID: { value: userIDs, sqltype: "integer" } };
result = queryExecute( sql, params );
A couple of benefits here:
- No need to specify
list: true
in the parameter definition: Lucee knows you're passing multiple values. - If your values were defined as an array to begin with, there's no need to convert them to a list.
Tags too
This will work with the <cfqueryparam>
tag as well.
<cfset userIDs = [ 1, 3 ]>
<cfquery name="result">
SELECT email FROM users WHERE ID IN ( <cfqueryparam value="#userIDs#" sqltype="integer"> )
</cfquery>