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.
Any chance of adding this type of functionality to the search on riaForge?