Thursday, June 24, 2010

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.

14 comments:

Roger said...

you have:

dynaCrat { params -&

etc etc

Imagine that needs to be

->

But if not... what the heck is -&

and if it is ->.. how'd ya mistype & for > hehe

Great article thanks!

Denoncourt's Blog said...

Thanks, Roger, I fixed that.
It should have been - ampersand g t semi-colon

初蓉初蓉 said...

知識可以傳授,智慧卻不行。每個人必須成為他自己。.................................................................

韻玉 said...

所有的資產,在不被諒解時,都成了負債....................................................................

貴寶 said...

向著星球長驅直進的人,反比踟躕在峽路上的人,更容易達到目的。............................................................

吳婷婷 said...

向著星球長驅直進的人,反比踟躕在峽路上的人,更容易達到目的。............................................................

云依恩HFH謝鄭JTR安 said...

你的分享很不錯.. 謝謝 ..................................................................

偉軍 said...

快樂與滿足的秘訣,就在全心全意投注於現在的每一分,每一秒上..................................................

吳婷婷 said...

Constant dripping wears away the stone. 滴水穿石!加油!..................................................................

宥妃宥妃 said...

More haste, less speed.............................................................

蔡靜芳蔡靜芳 said...

加油-不論如何都期待您的新發表!............................................................

香廷香廷 said...

做好事,不需要給人知道,雖然只是一件微不足道的事,但我相信,這會帶給我快樂。..................................................

淑娟淑娟淑娟 said...

精彩的文章是我停留的理由~..................................................................

WillianT_Smotherman0恆迪 said...

好的開始並不代表會成功,壞的開始並不代表是失敗..................................................