Boolean Searching with Phrase Matching in SQL

This function allows usage of advanced search options with the operators AND, OR, NOT, as well as quote based phrase matching. This was originally a custom tag written a long, long time ago by another person. Unfortunately, I no longer have the original author’s name and he seems to have disappeared. I have converted his tag to a function, made some logic changes here and there, modified deprecated code, and lastly, made some small efficiency changes. Fair warning. I have not done a lot of extensive testing.

The function setBoolSQLSearch

<cffunction name="setBoolSQLSearch" access="public" returntype="string" hint="sets search operators">
 <cfargument name="keyword" type="string" required="yes" />
 <cfargument name="dbFields" type="string" required="yes" />
 <cfargument name="boolOperator" type="string" required="no" default="and" />
 <cfscript>
  var fieldCount = 0;
  var searchString = "";
  var startBracketCount = 0;
  var endBracketCount = 0;
  var searchTermFlag = 1;
  var searchPortion = "";
  var counter = 1;
  var criteriaLen = 0;
  var prevBoolTerm = "";
  var dbFieldsCount = 0;
  var midSearch = "";
  var criteria = trim(arguments.keyword);
  var bracketPoint = find("(", criteria);
  var startBrackets = "";
  var endBrackets = "";
  var thisSearchTerm = "";
  while(bracketPoint neq 0){ //If there is an uneven number of brackets, remove them all. Otherwise, leave them.
   startBracketCount = startBracketCount + 1;
   bracketPoint = find("(", criteria, bracketPoint + 1);
  }
  bracketPoint = find(")", criteria);
  while(bracketPoint neq 0){
   endBracketCount = endBracketCount + 1;
   bracketPoint = find(")", criteria, bracketPoint + 1);
  }
  if(startBracketCount neq endBracketCount){
   criteria = replaceList(criteria, "(,)", ",");
  }
  criteriaLen = len(criteria);
  while(counter lte criteriaLen){
   //If this is the last searchterm, set the portion to the rest of the string
   if(counter eq len(criteria)){
    searchPortion = len(criteria);
   }
   else{ //otherwise find the next keyword
    searchPortion = find(" ", right(criteria, len(criteria) - counter));
    midSearch = mid(criteria, counter, searchPortion);
    if(find('"', midSearch) eq 1){
     //There is, so find the end quote.
     counter = counter + 1;
     searchPortion = searchPortion + find("""", right(criteria, len(criteria) - (counter + searchPortion))) + 1;
     criteria = removeChars(criteria, searchPortion +1 , 1);
     searchPortion = searchPortion - 1;
    }
    //if there are no keywords left, set the portion to the rest of the string
    if(searchPortion eq 0){
     searchPortion = len(criteria);
    }
   }
   //Check if this portion contains any boolean terms
   if((midSearch eq "OR" or midSearch eq "AND" or mid(criteria, counter, searchPortion) eq "NOT") and counter neq 1 and searchPortion neq len(criteria)){
   //Check if the current boolean term is just a NOT by itself (no AND or OR preceding it)
    if((prevBoolTerm neq "AND" and prevBoolTerm neq "OR") and midSearch eq "NOT"){
     //Append AND and the boolean term to the SQL string
     searchString = searchString & " AND " & midSearch & " ";
    }
    else{
     //Append this boolean term to the SQL string
     searchString = searchString & " " & midSearch & " ";
    }
    //Set the previous boolean term to the current boolean term
    prevBoolTerm = midSearch;
    searchTermFlag = 1;
   }
   else{
    fieldCount = 0;
    dbFieldsCount = listLen(arguments.dbFields);
    for(currentField = 1; currentField lte dbFieldsCount; currentField = currentField + 1){
     fieldCount = fieldCount + 1;
     //if there were no boolean terms pre-existing, add some
     if(searchTermFlag lte 0){
      //if there's more than one field to search on, OR the keyword
      if(fieldCount gt 1){
       searchString = searchString & " OR ";
      }
      else{ //otherwise, AND the keyword (by default), or whatever the boolOperator is set to
       searchString = searchString & " " & arguments.boolOperator & " ";
      }
     }
     //if this is the first field to search on, add an opening bracket
     if(fieldCount eq 1){
      searchString = searchString & "(";
     }
     //replace all ' single quotes with '' double quotes - safe parsing
     thisSearchTerm = replace(mid(criteria, counter, searchPortion), "'", "''", "all");
     startBrackets = "";
     endBrackets = "";
     //find any brackets at the start of the searchterm
     bracketPoint = find("(", thisSearchTerm);
     while(bracketPoint neq 0){
      startBrackets = startBrackets & "(";
      bracketPoint = find("(", thisSearchTerm, bracketPoint + 1);
     }
     //find any brackets at the end of the searchterm
     bracketPoint = find(")", thisSearchTerm);
     while(bracketPoint neq 0){
      endBrackets = endBrackets & ")";
      bracketPoint = find(")", thisSearchTerm, bracketPoint + 1);
     }
     //Remove the brackets from the searchterm
     thisSearchTerm = replaceList(thisSearchTerm, "(,)", ",");
     searchString = searchString & startBrackets & "(" & listGetAt(arguments.dbFields, currentField) & " LIKE '%" & thisSearchTerm & "%')" & endBrackets;
     //set the end of searchterm flag
     searchTermFlag = searchTermFlag - 1;
    }
   }
   if(searchTermFlag lte 0){
    searchString = searchString & ")";
   }
   counter = counter + searchPortion + 1;
  }
 </cfscript>
 <cfreturn searchString />
</cffunction>

Function Arguments:

The function accepts the following as parameters:

keywordThe search criteria. required.

dbFieldsA list of column names to search on. required.

boolOperatorJoining boolean operator, ie: AND, OR. optional. default: AND

Calling the function

<cffunction name="getSearchResults" access="public" output="false" returntype="struct" hint="get search results">
 <cfscript>
  var searchData = structNew();
  searchData['criteria'] = "";
  //Some preliminary code not included.
  if(structKeyExists(form, "criteria") and len(form.criteria) gt 0){
   searchData['criteria'] = form.criteria;
  }
  if(len(searchData.criteria) eq 0){
   searchData['status'] = false;
   searchData['statusMsg'] = "Please enter a search term.";
   searchData['dataFeed'] = serializeJSON(searchData);
  }
  else{
   //call the function
   searchData['searchTerm'] = request.utils.setBoolSQLSearch(searchData.criteria, "proj.p_name, proj.p_shortDesc, proj.p_fullDesc");
   //build the SQL call, which includes the searchData.searchTerm key from the function.
   searchData['sqlString'] = "select (convert(varchar, convert(money, sum(projFund.pf_amount)), 1)) as totalFunded, (convert(varchar, convert(money, (proj.p_fundingGoal - sum(projFund.pf_amount))), 1)) as amountToGo, count(distinct projFund.u_ID) as totalBackers, proj.p_ID, proj.p_name, proj.p_shortDesc, proj.p_image, proj.p_url, proj.p_urlTitle, (convert(varchar, convert(money, proj.p_fundingGoal), 1)) as p_fundingGoal, org.o_name, org.o_url, proj.p_createdDate, proj.p_updatedDate, proj.p_endDate from tbl_projects proj left join tbl_projectFunding projFund on proj.p_ID = projFund.p_ID inner join tbl_organizations org on proj.o_ID = org.o_ID where proj.p_active = 1 and (" & preserveSingleQuotes(searchData.searchTerm) & ") group by proj.p_ID, proj.p_name, proj.p_shortDesc, proj.p_fullDesc, proj.p_image, proj.p_url, proj.p_urlTitle, proj.p_fundingGoal, org.o_name, org.o_url, proj.p_createdDate, proj.p_updatedDate, proj.p_endDate";
   //call a simple getQuery function which just makes a call to cfquery, also shown below.
   searchData['searchResults'] = request.utils.getQuery(searchData.sqlString);

The getQuery function.

<cffunction name="getQuery" access="public" returntype="query">
<cfargument name="sqlString" type="string" required="yes">
<cfargument name="maxRows" type="string" required="no" default="">
 <cfif len(arguments.maxRows)>
  <cfquery name="recordSet" datasource="#request.dataSource#" maxrows="#arguments.maxRows#">
   #preserveSingleQuotes(arguments.sqlString)#
  </cfquery>
 <cfelse>
  <cfquery name="recordSet" datasource="#request.dataSource#">
   #preserveSingleQuotes(arguments.sqlString)#
  </cfquery>
 </cfif>
<cfreturn recordSet />
</cffunction>

That’s it. I’ll show a better example of using it later on……

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s