DRY with Grails namedQueries and a Super-Dynamic Search Strategy

I just finished developing a simple query engine with Grails 1.2.1 for one of my clients. As usually, Grails did most of the hard work. The requirement was to provide a query engine for customer-specific flat files that contained all information about transactions that occurred in the last year. Previously the flat files were delivered to the clients as Microsoft Access binary files. The problem was that the clients never took the initiative to write apps to search and aggregate the information. So I was brought in to use Grails to rapidly develop a web application that provided search and aggregation of the client-specific web flat files.

The resulting Grails application is hosted on Amazon EC2 with MySQL. Anyway, I wanted to share with you my use of Grails 1.2's namedQueries and my simple strategy to support a dynamic search in the standard list.gsp.

The application's list page shows all the columns of the table. To do that it needs to know the properties of the domain. Each client has a custom domain class specific to their flat file (which, for some reason, have different column names.) The domain classes have a getColumnProperties():

static List getColumnProperties() {
def columnProperties = []
CustDomainName.properties.declaredFields.each {prop ->
if (prop.modifiers == 2            &&
!NO_SHOW_COLS.find {it == prop.name} ) {
columnProperties << prop
}
return columnProperties
}

The column properties are passed to list.gsp as [properties:domain.columnProperties] so the page can list all columns:

<tbody>
<g:each in="${domainInstances}" status="i" var="domainObj">
<tr class="${(i % 2) == 0 ? 'odd' : 'even'}">
<g:each in="${properties}" var="property">
 <td>${domainObj[property.name]}</td>
</g:each>
</tr>
</g:each>
</tbody>

Each column has a search added to the the top of the table:

<tr>
<g:each in="${properties}" var="property">
<td>
<% if (property.type.name != 'java.util.Date') { %>
<input type="text" name="${property.name}" value="${(params[property.name])}"
<% if (property.type.name == 'java.lang.String') {%>
    title="Enter a character prefix to filter ${property.name}"
<% } else { /* assume numeric */ %>
   title="Enter a numeric value to filter ${property.name}"
<% } %>
/>
<% } else { %>
<richui:dateChooser format="MM/dd/yyyy" name="${property.name}" value="${(params[property.name])}" />
<% } %>
</td>
</g:each>
</tr>
<tr>
<g:each in="${properties}" var="property">
<td>
<% if (property.type.name == 'java.util.Date') { %>
 <richui:dateChooser format="MM/dd/yyyy" name="${property.name}To" value="${(params[property.name+'To'])}"  />
<% } else if (property.type.name != 'java.lang.String') {
 def propOp = property.name+'_Op'
%>
<g:select name="${propOp}" value="${params[propOp]}"
   from="${['eq', 'gt', 'ge', 'lt', 'le', 'ne']}"
   valueMessagePrefix="critera.operator"  />
<% } %>
</td>
</g:each>
</tr>

Note how dates have a from and to with the RichUI dateChooser and numerics have operators. Strings work as prefixes to the SQL like clause. The list action is then implemented as follows:

def list = {
params.max = Math.min(params.max ? params.int('max') : 10, 100)
params.offset = params.offset ? params.int('offset') : 0
params.sort = params.sort?:'id'
params.order = params.order?:'desc'

List domainInstances = domain.dynaCrit(params) // .list(params) ignors sort
    .findAllByIdGreaterThan(0,
         [max: params.max, offset:params.offset,
          sort:params.sort, order:params.order])

int count = domain.dynaCrit(params).count()

[domainInstances: domainInstances, count:count,
properties:domain.columnProperties, params:params]
}

Prior to namedQueries (with Grails 1.2.) I've always had to copy-and-paste criterion logic from the search to the count query. This violated the Don't Repeat Yourself (DRY) principle. Now I use a named query. Note that I did have an issue with the sort facilities in the list method so I did a hack with the findAllByIdGreaterThan (perhaps this is fixed with Grails 1.3.) Finally, here's the namedQuery in the domain class:

static namedQueries = {
dynaCrit {params ->
params.each {filterProp ->
  if (filterProp.value) {
    def property = CustDomainName.columnProperties.find {it.name == filterProp.key}
    if (property) {
      if (property.type == String) {
        ilike ("$property.name",  filterProp.value+'%')
      } else if (property.type == Date) {
        between ("$property.name",  filterProp.value, params[filterProp.key+'To'])
      } else {
        "${(params[filterProp.key+'_Op'])}" ("$property.name",  property.type.newInstance(filterProp.value.trim()))
      }
    }
  }
}
}
}

The application does provide a number of other handy features:

• The ability on the list page to remove (and re-add) columns
• Dynamic summary reports with prompt pages prompting for groupBy and sum columns along with filter criterion.
• Excel download option of the user-defined report
• The ability to save user-defined queries.

What I found amazing during the development of this application was how Grails made it ridiculously easy to develop dynamic reporting.