"AND" / "OR" Keyword Search

I know that verity includes a lot of advanced search features, but I honestly just haven't worked with it much. So I'm not sure if verity offers an "and/or" keyword feature, though I wouldn't be surprised. Then again verity requires you to create and maintain collections and that means double-maintenance if the data is stored in your database and you don't always need the other features verity has. So a while back I decided I wanted the ability to include and/or keywords in searches, (similar to google I suppose) and rather than copying and pasting that code every time, the framework's SQL abstraction layer gave me the ability to simplify the process of creating those and/or filters. The framework code looks something like this:

<cfparam name="attributes.search" type="string" default="" />
<cfif len(trim(atributes.search))>
<cfset search = Datasource.getStatement("select").init("tbl_Content") />
<cfset search.andOrFilter("title,keywords,contentText",attributes.search) />
<cfdump var="#search.execute()#" />
</cfif>

This code then takes whatever you've entered in the search form, breaks it apart into phrases separated by the words "and" and "or"1 and spreads those phrases across the title, keywords and contentText columns in the tbl_content table. Being able to do this with just one line of code is awfully convenient. The resultant query is something like this:

searched for "fish and chicken"

select * from tbl_Content
where (
(title like '%fish%'
or keywords like '%fish%'
or contentText like '%fish%')
and
(title like '%chicken%'
or keywords like '%chicken%'
or contentText like '%chicken%')
)

So with this sql output, you can see that the query will return any record that contains both words, but it also returns results where the words are in different portions of the content, for example the word "fish" in the title and "chicken" in the contentText, so if there's an article titled "let's go shark fishing" with the phrase "are you chicken?" in the contentText, this search will return that record. What makes this really nice however is that as the number of columns increase and/or the complexity of the search string increases, the SQL statement becomes very large and very complicated rather quickly, however, the andOrFilter facade which handles the thankless task of creating the SQL syntax never becomes any more complicated than the one simple line above2.

<cfset search.andOrFilter("title,keywords,contentText",attributes.search) />

Today I realized that the facade was limited to only accessing columns in a single table, so although it worked it was still not as flexible as I wanted it to be. I was working on a contact manager and needing to include two columns from joined tables in the and/or column list. So I updated the facade to allow that and now you can also do this:

<cfset search.join("author a") />
<cfset search.andOrFilter(
"title,keywords,contentText,a.firstname,a.lastname"
,attributes.search) />

It was a really good thing for me, because the query I was working on was massive... Check this out: 3

Aside from the fact that the sql abstraction layer escapes all the column names for me and ensures case insensitivity (even with a case-sensitive database collation), creating this query required me to write a remarkably small amount of actual code, much of which is even reused in other queries.

So anyway, I uploaded a new archive with the enhancement for multi-table and/or filters4.

  1. Actually it's a bit more sophisticated than that even because it uses localized strings for the and/or keywords for international use, so for example, german users can use the keywords "und" and "oder" in their search.
  2. And really, without a facade like this, and/or keyword searching is one of those features that just never gets implemented because, as nice as it may be for the users, it's tedious and never makes it into the list of priorities we can spend time working on.
  3. Thanks to search.getSyntax() for the sql syntax - this lets you output the syntax instead of executing the query for use in generating scripts to be executed elsewhere, as in my case it's easier to debug a query sometimes by copying and pasting the SQL syntax into an IDE provided by the database vendor like Query Analyzer. This way I don't have to sit through the tedium of copying all the query-param values, sifting through the query and replacing question-marks, because getSyntax() performs the merge for me.
  4. I also discovered that when using filterGroups (they allow you to wrap parenthesis around groups of filters like in the query above) if you misspelled the name of a table alias, it was producing an unusual error -- it's supposed to throw a custom "column not found" error, which it wasn't doing, so I fixed that also.

Comments
BlogCFC was created by Raymond Camden. This blog is running version 5.5.006. | Protected by Akismet | Blog with WordPress