DataFaucet ORM

Reverse Filter

Reverse filtering is something that's not done very often simply because there's not often a need for it. Occasionally it's handy to have a reverse filter for finding records where a column containing a column-delimited list has a specific value in the list (usually considered bad normalization), or for creating natural language searching where a user enters a question like "How do I house train my dog?" or something like search-hints for synonyms or commonly misspelled words or phrases. The latter of these supports something like Google's hints that say:

No results found for "emphatic duck".

Did you mean "anaphylactic shock"?

The reverse filter works essentially the same way the the standard filter works, but it reverses the order of the column and content. So for example where a standard filter would find "dog" in the column "animalName", a reverse filter would find the contents of the "animalName" column in the user provided string "How do I house train my dog?".

A standard filter in a SQL statement looks like this:

<cfquery datasource="#MyDSN#" username="#dbUsr#" password="#dbPwd#">
SELECT * FROM Animals 
WHERE animalName LIKE '%#form.animalName#%'
</cfquery>

A reverse filter looks like this:

<cfquery datasource="#MyDSN#" username="#dbUsr#" password="#dbPwd#">
SELECT * FROM Animals 
WHERE '#form.Question#' LIKE '%' + animalName + '%'
</cfquery>

The statement object has a reverseFilter() method for convenience, which has the same arguments as the standard filter() method except instead of the comparison operator where you would normally specify = or LIKE as the method of comparison, all reverse filters use the LIKE operator and the comparison argument is replaced with an "include" (and/or) argument and a "delimiters" argument for creating a list-driven filter like this:

<cfset stmt.reverseFilter(
  column="listColumn",
  content=form.userSelection,
  delimiters=",") />

Which creates a query like this:

<cfquery datasource="#MyDSN#" username="#dbUsr#" password="#dbPwd#">
SELECT * FROM MyTable 
WHERE ',' + listColumn + ',' LIKE '%,#form.userselection#,%'
</cfquery>