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)> </code>

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)> </code>

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.

Raymond Camden's Picture

About Raymond Camden

Raymond is a developer advocate. He focuses on JavaScript, serverless and enterprise cat demos. If you like this article, please consider visiting my Amazon Wishlist or donating via PayPal to show your support. You can even buy me a coffee!

Lafayette, LA https://www.raymondcamden.com

Comments