Check if a Value Exists in a Query

Sometimes you'll need to know if a certain value exists in a query. For example, let's say I have a query of colors.



For demonstration purposes, let's manually build the query:

<cfset colors = queryNew("color") />

<cfloop list="blue,red,green,yellow,blue,black,red,orange" index="color">
 <cfset queryAddRow(colors) />
 <cfset querySetCell(colors,"color",color) />
</cfloop>

Now let's say I wanted to know if the color "green" exists in my query. There are quite a few ways to do this.

One way to do this would be to create a list of all the colors, then check to see if "green" exists in the list.

<cfset colorList = valueList(colors.color) />

<cfif listFindNoCase(colorList,"green")>
 green exists
<cfelse>
 green does not exist
</cfif>

However, converting the values to a list might cause problems if one of the values has a comma in it. Granted that's not the case in this scenario, but it's worth mentioning.

Another problem might come up if the column name is dynamic. For example, the following code won't work.

<cfparam name="url.column" default="color" />

<cfset colorList = valueList(colors[url.column]) />

<cfif listFindNoCase(colorList,"green")>
 green exists
<cfelse>
 green does not exist
</cfif>

I'm not sure how often this situation comes up, but here's a trick to be able to use dynamic column names: use arrayToList() rather than valueList().

<cfparam name="url.column" default="color" />

<cfset colorList = arrayToList(colors[url.column]) />

<cfif listFindNoCase(colorList,"green")>
 green exists
<cfelse>
 green does not exist
</cfif>

Another way to check if "green" exists in the query would be to use a query of a query.

<cfquery name="getGreen" dbtype="query">
 select *
 from colors
 where color = 'green'
</cfquery>

<cfif getGreen.recordCount gt 0>
 green exists
<cfelse>
 green does not exist
</cfif>

However, queries of queries can potentially hurt your application's performance if called multiple times. Plus, they're case-sensitive.

In order to increase performance, you could loop over the colors query and insert the values into a struct, then check to see if the key exists in the struct.

<cfset colorStruct = {} />
<cfloop query="colors">
 <cfset colorStruct[color] = true />
</cfloop>

<cfif structKeyExists(colorStruct,"green")>
 green exists
<cfelse>
 green does not exist
</cfif>

While this should help your application's performance, there's still the issue of case-sensitivity. Fortunately, ColdFusion 9 helps solve this problem with the addition of arrayFindNoCase.

Once again, let's treat the query as an array. Normally accessing query["column"] is handled the same as query["column"][1] and would only return the value from the first record in the query, but apparently it references the entire array if used inside a function call.

<cfif arrayFindNoCase(colors["color"],"green")>
 green exists
<cfelse>
 green does not exist
</cfif>

Not only is this approach case-insensitive, but it's also the least amount of code. Win-win.