DataFaucet ORM

Filter Group

A filter group is a convenient way of grouping several filters together. Filters in a group are bound with parenthesis by default, allowing them to be treated as a whole separate from the inclusivity of other filters in the statement.

Wha?!

Say you had a statement to get products with two filters on productprice. These filters are inclusive, creating sql syntax that looks like this:

SELECT * FROM [DataWarehouse].[DBO].[tblProduct] [product]
WHERE [product].[productprice] >= 20 
AND [product].[productprice] <= 50

This query gets all products in the specified category between $20 and $50.

Now lets say the user wanted to filter these products further by typing a search phrase. He enters two words however, he wants to see any product in the catalog related to either word, not both of them together. For example, he might be looking for posters featuring dolphins or unicorns. If you simply added filters directly to the statement, you would get this:

SELECT * FROM [DataWarehouse].[DBO].[tblProduct] [product]
WHERE [product].[productprice] >= 20 
AND [product].[productprice] <= 50
AND [product].[productdescription] LIKE '%dolphin%' 
AND [product].[productdescription] LIKE '%unicorn%'

See the problem? He's not going to get all the products he wants to see because that query will only show him posters with both dolphins AND unicorns. You also can't set the include property of the select statement to "OR" because that would show him products outside his price range (more than $20 OR less than $50 would be ALL products, effectively cancelling out his price range entirely).

One solution is to add a filter group.

The code looks like this:

<cfscript>
  // get the datasource
  ds = request.DataFaucet.getDatasource(); 
  // create a select statement 
  stmt = ds.getSelect("*","tblProduct product"); 
  
  // filter by price 
  stmt.filter("productprice",form.lowprice,">="); 
  stmt.filter("productprice",form.highprice,"<="); 
  
  // create a filter group 
  group = stmt.filterGroup("or"); // use or for exclusive filters 
  
  // add the search filters 
  search = listToArray(form.search," "); 
  for (x = 1; x lte arrayLen(search); x = x + 1) { 
    group.filter("productDescription","%#search[x]#%","LIKE"); 
  } 
  
  // get the results 
  query = stmt.execute(); 
</cfscript>

And the above code will generate SQL syntax like this:

SELECT * FROM [DataWarehouse].[DBO].[tblProduct] [product]
WHERE [product].[productprice] >= 20 
AND [product].[productprice] <= 50 
AND 
  (UPPER([product].[productdescription]) LIKE '%DOLPHIN%' 
  OR UPPER([product].[productdescription]) LIKE '%UNICORN%')

The above code however is rather verbose, which is why DataFaucet also includes several other methods of applying filters which are more succinct, such as collection filters, numeric filters and and/or filters.