Filters: Reusing SQL Clauses

Post date: Mar 22, 2012 5:6:25 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:

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:

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:

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:

</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:

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.