Sponsors

Links

Subscribe

Enter your email address to subscribe to this blog.

Archives by Subject

• Lap Band Surgery

Transfer Query - From Phrase to AND Search

Not sure if anyone will find this helpful, but here goes. A user reported some dissatisfaction with the search engine at CFLib. Specifically a search for FOO GOO would result in an exact phrase match on foo goo. It would not match a UDF named MyFooMilkshakeBetterThanGoo.

This was because my search did the rather simple %SEARCH% style match (ie, match the search term exactly, case insensntive). Here is the original Transfer code I used for the search:

<cfsavecontent variable="sql">
from udf.udf
where (udf.udf.name like :search
or udf.udf.shortdescription like :search
or udf.udf.description like :search)
and udf.udf.released = :active
order by udf.udf.name asc
</cfsavecontent>

<cfset q = variables.transfer.createQuery(sql)>
<cfset q.setParam("search", "%" & arguments.search & "%", "string")>
<cfset q.setParam("active", true, "boolean")>
<cfset q.setCacheEvaluation(true)>

Even if you don't know Transfer, this should be readable to you. I do a match on either name, shortdescription, or description. If you search for FOO, then basically it ends up being a search on %FOO%. Again though this kind of breaks down when you search for multiple words.

I wanted to keep things simple, so I decided that any multiword search would be an AND style search (ie, all the words must match), and I'd split on an empty space. I rewrote the TQL like so:

<cfsavecontent variable="sql">
from udf.udf
where (
   <cfloop index="idx" from="1" to="#arrayLen(words)#">
   <cfoutput>
   (udf.udf.name like :word#idx#
   or udf.udf.shortdescription like :word#idx#
   or udf.udf.description like :word#idx#
   ) <cfif idx lt arrayLen(words)>and</cfif>
   </cfoutput>
   </cfloop>
)
and udf.udf.released = :active
order by udf.udf.name asc
</cfsavecontent>
   
<cfset q = variables.transfer.createQuery(sql)>
<cfloop index="idx" from="1" to="#arrayLen(words)#">
   <cfset q.setParam("word#idx#", "%" & words[idx] & "%", "string")>
</cfloop>
<cfset q.setParam("active", true, "boolean")>
<cfset q.setCacheEvaluation(true)>

First off, the variables words comes from this change in the function header:

<cfset var words = listToArray(arguments.search," ")>

So a search for FOO GOO results in words being equal to ["FOO","GOO"]. Notice then my loop over the array. I didn't use the new array style cfloop as I wanted a counter variable I could check to see if I needed an AND at the end of each block.

The next change was to have a dynamic set of setParams. This will replace word1, word2, etc, with the proper value from the array.

A good test for this is a search for "host url". Before the change it returned nothing. Now it matches getCurrentURL, getHostFromURL, and getHostFromURLJava.

Comments (7) Sep 23, 2008 8:42 AM Views: 1357

Comments