Monday, December 7, 2009

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.

5 comments:

  1. loop over the query and insert it to struct? I don't think that's an optimization at all unless you're counting.

    ReplyDelete
  2. @Henry, I figured that would be confusing. Let's say you have a list of 4 colors and you want to check if each one of those colors exists in the query. Based on my primitive tests using getTickCount(), it's actually quicker to loop the entire query first to populate a struct for key lookups than it is to loop the list of 4 colors and perform a query of a query, since creating the struct only happens once while the QoQ happens once for each loop.

    ReplyDelete
    Replies
    1. Thanks for this clarification, this puzzled me a bit at first.

      Delete
  3. ArrayFindNoCase not only enables one to search a query for a specific value, it also presents the opportunity to access other properties of a value's record. This is possible as ArrayFindNoCase returns the array index of the value which conveniently is the row number of its contained record. An example being: #colors.name[arrayFindNoCase(colors["color"],"green")]#

    Great post Tony!

    ReplyDelete