Filters: Reusing SQL Clauses

posted Mar 22, 2012, 10:06 AM by Eric Patrick   [ updated Dec 18, 2013, 9:34 PM ]

Background

Frequently when searching or creating a dashboard for a table, there a client-driven or performance-driven requirements to filter out a subset of data. Examples include:
  • When searching Foreclosures, one typically is worried about 'active' foreclosures
  • When searching Loans, one typically is worried about 'active' loans
  • For Smart Worklists, one often want to filter out 'completed' or 'skipped' items
The SqlPattern class supports the concept of "filters" which can be used to simplify code maintenance from SQL statements up through the application stack to a web client. 

Simple Example

All classes can leverage Abstract.config's 'Search' statement, which looks something like:

SELECT ... FROM {From.ForeignKey} WHERE {Where.Basic} {Extranet.Default} ORDER BY {OrderBy.Parameters}

We can narrow this result set by defining a filter called "Active" like this:

<Filter Name="Active" Clause="{Table}.UpdatedDate > '1/1/2001'"/>

and then modifying the Search statement to include:

SELECT ... FROM {From.ForeignKey} WHERE {Where.Basic} AND {Where.Active} {Extranet.Default} ORDER BY {OrderBy.Parameters}

From there, each class can use the standard Active filter by default, or can override the definition of Active. Foreclosure might look like this:

<Filter Name="Active" Clause="Process.DateClosed IS NULL"/>

This enables all classes to leverage the generic "Search" statement, while tweaking it's WHERE clause for class-specific purposes. Importantly, if we change the generic "Search" statement in Abstract.config, all classes leveraging this will inherit this change, but retain their customized definition of "Active".

Advanced Example

Pre-defining {Where.Active} in the Search statement does not meet all use cases, however. Examples include:
  • Show all Foreclosures opened in the last 90 days, even if they're currently closed (not "active")
  • Show all "Started" Smart Worklist Members assigned to the QC Review team
  • Show all Valuations that are not "cancelled" or "hidden"
In this case, one can simplify the parameters passed to a query by:
  • using {Where.Filters} instead of hard-coding {Where.Active}
  • passing a SqlFilters parameter containing the names of the filters you want applied
So, the Abstract.config's Search statement is changed to:

SELECT ... FROM {From.ForeignKey} WHERE {Where.Basic} AND {Where.Filters} {Extranet.Default} ORDER BY {OrderBy.Parameters}

Taking the Smart Worklist Member class as an example, we can define the following filters:

<Filter Name="Active" Clause="SmartWorklist.CompletionDate IS NULL AND SmartWorklist.CancelledDate IS NULL"/>
<Filter Name="Pending" Clause="SmartWorklistMember.StartDate IS NULL AND ... AND SmartWorklistMember.SkipDate IS NULL"/>
<Filter Name="Started" Clause="SmartWorklistMember.StartDate IS NOT NULL AND ... AND SmartWorklistMember.SkipDate IS NULL"/>
<Filter Name="Skipped" Clause="SmartWorklistMember.SkipDate IS NOT NULL AND ... AND SmartWorklist.CancelledDate IS NULL"/>

At this point, one can invoke:
  • SmartWorklistMember.ashx/Search?AssignedPersonID=1 // everything assigned to admin@quandis.com
  • SmartWorklistMember.ashx/Search?AssignedPersonID=1&SqlFilters=Active // everything part of an active worklist assigned to admin@quandis.com
  • SmartWorklistMember.ashx/Search?AssignedPersonID=1&SqlFilters=Active,Started // everything part of an active worklist started by admin@quandis.com
If you want to apply the "Active" filter by default, you may define the "default" SqlFilters used by a query with a Parameter. The current Abstract.config has this:

<Statement Name="Search" Transform="{Table}.Search.xslt" Query="
...
SELECT ... FROM {From.ForeignKey} WHERE {Where.Basic} AND {Where.Filters} {Extranet.Default} ORDER BY {OrderBy.Parameters}
..."/>

The SmartWorklistMember.config "overrides" this with:

<Statement Name="Search">
<Parameters>
<Parameter Name="SqlFilters" DbType="String" Value="Active"/>
</Parameters>
</Statement>

This modifies the system behavior as follows:
  • SmartWorklistMember.ashx/Search?AssignedPersonID=1 //  everything part of an active worklist assigned to admin@quandis.com
  • SmartWorklistMember.ashx/Search?AssignedPersonID=1&SqlFilters=Active // same as above
  • SmartWorklistMember.ashx/Search?AssignedPersonID=1&SqlFilters=Active,Start // everything part of an active worklist started by admin@quandis.com
  • SmartWorklistMember.ashx/Search?AssignedPersonID=1&SqlFilters= //  ignore the default Parameter; return everything assigned to admin@quandis.com

Automatic Filters

If a Filter specified Parameters, and each of the specified parameters is present in the dictionary of parameters, a {Where.Basic} clause will automatically include a parameter. This is useful in situation where you wish to join to data is that is not part of the {From.ForeignKey} clause. For example:

<Filter Name="InDecisionID" Clause="{Table}.{Table}ID IN (SELECT ObjectID FROM DecisionStep WHERE DecisionID = @InDecisionID AND Object = '{Table}')" Parameters="InDecisionID" />

With such a filter, the following URL will limit results to ImportForm records that are part of workflow with ID 2401:

ImportForm.ashx/Search?InDecisonID=2401

will result in a WHERE clause that includes:

WHERE ... AND ImportForm.ImportFormID IN (SELECT ObjectID FROM DecisionStep WHERE DecisionID = @InDecisionID AND Object = '{Table}')

From a performance perspective, this is preferable to:

WHERE ...
  AND (@InDecisionID IS NULL OR ImportForm.ImportFormID IN (SELECT ObjectID FROM DecisionStep WHERE DecisionID = @InDecisionID AND Object = '{Table}'))

The presence of the ImportForm.ImportFormID clause may contribute to parameter sniffing, causing the SQL optimizer to choose an execution plan that works well with the presents of an @InDecisionID parameter, but poorly without one.  By including this clause dynamically with a filter, a different optimization plan is guaranteed for the query with and without the @InDecisionID parameter.

When building complex WHERE clauses, it is preferable to build statements with {Where.Basic} and several parameter-based filters, to a single complex statement that handles the same parameters that may be NULL.

Useful Filters

AbstractObject.config contains some standard, useful filters.

<!-- Limit results matching a linked record -->
<Filter Name="LinkedFrom" Clause="{Table}.{Table}ID IN (SELECT LinkedObjectID FROM ObjectLink WHERE ObjectLink = @LinkedFrom)" Parameters="LinkedFrom" />
<Filter Name="LinkedTo" Clause="{Table}.{Table}ID IN (SELECT PrimaryObjectID FROM ObjectLink WHERE ObjectLink = @LinkedTo)" Parameters="LinkedTo" />
<!-- Limit results matching items in a workflow -->
<Filter Name="InDecisionID" Clause="{Table}.{Table}ID IN (SELECT ObjectID FROM DecisionStep WHERE DecisionID = @InDecisionID AND Object = '{Table}')" Parameters="InDecisionID" />


Comments