Skip to end of metadata
Go to start of metadata

Last week I was into QueryBuilder and was able to do some improvements, refactorings and understand it for a while (see QueryBuilder matchAny() and jql() expressions).  Understanding some bits of QB is fleeting, so while I was in that head space, I make a few additional changes.

Sift (Closure)

I had implemented the jpl() expression which allows arbitrary JPQL expressions to be included in a QB specification. I'm still not sure it's a good idea or useful enough.  But, if it is, it will also be useful to have a post-filtering option corollary. The {{sift()}} expression allows you to specify a Closure<Boolean> to be included as a post-filtering predicate.  I chose "sift" because "filter" seemed too overloaded and easier to find if it needs to be removed.  For example:

builder.query { 
   name.like "smith"
   sift { getCalculatedValue() > getPersistedValue() }
}

Sift is not technically necessary, because it's already possible to addFilter() after the builder is created. But this seems a bit more natural and will be possible to expose to end users via the Expert Query.  

The closure will be passed the top-level entity that is being filtered and the argument is also the "first delegate".  So specifying "it" is optional.  When query spec is itself a Closure, it has full access to the API (e.g. it could call an autowired repository, ObjectFactory or any other utility class).  But when the spec is a String, it's limited by the QB's compiler whitelist (i.e. normal security limitations apply).

USAS Account "filter" QueryHelper

Working on sift() caused me to realize that post-filtering QueryHelper's might be even more useful.  So I tweaked the QueryHelper API to support non-persistent helpers which provide a post-filtering predicate.  The first of these is in USAS's AccountFilterQueryHelper (USASR-2940).  The helper adds a queryable "filter" property to Account and BudgetBase. It can be used with ".eq" or ".ne" operations specifying the name of the USAS Filters object to use as the post filtering (Filters is the aggregate used for wildcard security account filters). 

Instead of checking some property for a value, it uses the argument to load the Filters object by name, and then creates a predicate which tests the account code against the filter.

For example, starting with PurchaseOrderCharge, you could find all charges which match a given Filter object with:

builder.query {
   account.filter.eq "MyFilter"
}

Because "filter" is a normal  QueryHelper, the user can specify it via the UI and the argument could be replaced with a param function.

Also, when used with matchAny it could be used to select records which match one or more filters:

builder.query {
   matchAny {
      account.filter.eq param("filterName1")
      account.filter.eq param("filterName2")
      account.filter.eq param("filterName3")   
   }
}


Advanced Query/Expert Query

While I was at it, I wanted to at least be able to use matchAny in the AdvancedQuery UI.   Previously, the AQ interface was limited to property expressions ( the property existed and be in the form property.operation arg.  It also prevented a single property from appearing in the query twice.  To make matchAny at least somewhat useful, I made these changes to the AQ and Expert Query:

  • You can now manually enter matchAny into the Expert Query box and it will preserved in the Advanced Query.  Once the matchAny is in the property filters, it will be round-tripped correctly between the Advanced and Expert queries.   I didn't have time (or ambition) to do a full UI.  So you can only enter the matchAny in the Expert Query.  Once it's there, you can add/re-order properties using the normal AQ UI.  Also, I didn't deal with the "end" of the matchAny, so all of the expressions after the matchAny are part of the "OR" operation. So the user needs to know that the order is important.  We'll likely need a more sophisticated UI later.  But this should suffice for creating template reports in the meantime.
  • A single property can be now be specified mulitple times in a single query.  This is particularly useful when using matchAny (like in the example above) when wanting to use the same property but with different operations.  

Below is a screen shot of how it looks.  

I didn't try to deal with sift() or jpl() expressions.  Partly because I'm not certain it's a good idea, but also because it was harder....

 


  • No labels