Grails: Dynamically building criteria queries

** Update: Detached Criteria are probably the better way of achieving this type of DRY principle now.  They became available in Grails 2.0; I wrote this post while using Grails 1.x. **

** Update 2: DetachedCriteria have some limitations. I wrote a cleaned up and expanded version of what this post discusses: Criteria Aggregator: Dynamic Criteria Queries in Grails **

Ok, I’ve been exploring using Groovy and Grails at work to make our team more efficient. Java is great and all, but it’s a terrible language for web development. For some quick info: A group of people decided Java could be a good web development language if it weren’t so Java-y. So they wrote Groovy. It’s fully compatible with Java, but is actually a dynamically-typed scripting language built on top of Java. Great, now we have a good web language with all the support and power of Java. Groovy compiles to the same bytecode as Java, because Java is Groovy is Java.

Well, then Ruby took the web development world by storm with the Rails architecture. Finally web developers could focus on the actual interesting part of the job rather than continually writing frameworks to run their sites. It was such a good idea that it was very quickly mirrored into other languages. Python got Django (my personal favorite and the future of the 100 Hour Board) and Groovy got Grails.

Anyway, if you’ve found this post by Googling you probably know all that so let’s get to the meat of the matter.

In my effort to learn Groovy and Grails I wanted to be able to build up Criteria Queries dynamically. Django lets you do this incredibly easily by passing around queryset objects that have lazy loading. Grails interfaces with Hibernate using some convenient and easy closure work with access to the Hibernate Criteria Builder. So you can easily and quickly define your query but only within that closure that gets passed to the builder. I wanted to be able to piece my query together from other pieces in order to adhere to the Don’t Repeat Yourself (DRY) principle.

In my searching I came upon a blog post by Geoff Lane at zorched.net entitled “DRYing Grails Criteria Queries“. His method involves extending the metaclass of the HibernateCriteriaBuilder object using Groovy’s expando abilities. This is a decent solution, but not incredibly flexible. I needed something better.

For 3 days I relentlessly scoured the Internet searching for answers, I coded all sorts of tests, I digested the source code of the Grails HibernateCriteriaBuilder class, I poked, I prodded, I made wild accusations. All in vain.

I decided that one of the following conclusions must be true:
1. It was so obvious that I just couldn’t see it.
2. I was too stupid to come up with a good solution.
3. I was asking the wrong question.
4. I needed more experience with Groovy and working with closures.

Now, I’m not entirely convinced that #3 isn’t true, but #4 also happened to be true. I found my solution this morning when I actually paid attention to what the error message was really telling me rather than just “it didn’t work”.

So, without further ado, here is how you can achieve DRYness with Grails when dynamically building criteria queries, without dropping all the way back into Java.

For the sake of this example let’s assume we have an Account class with the properties “accountType”, “accountAction”, and “owner”.

Our automatically generated code to list objects would look something like this (in our AccountController.groovy file):

def list = {
  params.max = Math.min( params.max ? params.max.toInteger() : 20,  100)
  params.offset = params.offset ? params.offset.toInteger() : 0

  def results = Account.createCriteria().list(max:params.max, offset:params.offset) {
    order(params.sort ? params.sort : "id", params.order ? params.order : "asc")
  }
  [ accountInstanceList: results, accountInstanceTotal: results.totalCount ]
}

That’s all well and good, but let’s add the ability to sort based on the name of the account owner, and display only accounts belonging to a specific user:

def list = {
  params.max = Math.min( params.max ? params.max.toInteger() : 20,  100)
  params.offset = params.offset ? params.offset.toInteger() : 0
  def req_owner = User.get(params.id)

  def results = Account.createCriteria().list(max:params.max, offset:params.offset) {
    if (params.sort == 'userName') {
      owner {
        if (req_owner) {eq("id", req_owner.id)}
     order("lastName", params.order ? params.order : "asc")
     order("firstName", params.order ? params.order : "asc")
      }
    } else {
      if (req_owner) {owner {eq("id", req_owner.id)}}
      order(params.sort ? params.sort : "id", params.order ? params.order : "asc")
    }
  }
  [ accountInstanceList: results, accountInstanceTotal: results.totalCount ]
}

Still fine, but what if we want to build off of this for different controller methods? If I want a page that does the same thing, but limits the display to only accounts of a certain account type I can use the method described in the link above by Geoff Lane. But suppose I want to further provide another page that limits it further or several pages that provide just slightly different listings based on criteria changes. Perhaps there is a great easy way to do this in Grails, and if so, please comment and tell me, because I couldn’t find it.

Here’s how I do it. First let’s pull the code we have to a reusable location. We make a self-standing closure and then curry the “params” variable to it:

def account_list = {params ->
  def req_owner = User.get(params.id)
  if (params.sort == 'userName') {
    owner {
      if (req_owner) {eq("id", req_owner.id)}
      order("lastName", params.order ? params.order : "asc")
      order("firstName", params.order ? params.order : "asc")
    }
  } else {
    if (req_owner) {owner {eq("id", req_owner.id)}}
    order(params.sort ? params.sort : "id", params.order ? params.order : "asc")
  }
}

def list = {
  params.max = Math.min( params.max ? params.max.toInteger() : 20,  100)
  params.offset = params.offset ? params.offset.toInteger() : 0

  def results = Account.createCriteria().list(max:params.max, offset:params.offset, account_list.curry(params))
  [ accountInstanceList: results, accountInstanceTotal: results.totalCount ]
}

The trouble is when we try to combine self-standing closures.

Everytime I tried something that seemed like it should work I’d get errors about “AccountController.isNotNull not applicable for arguments…” or whatever it said. I didn’t think hard enough about what it was really saying to me. The additional closure pieces were getting the wrong scope for whatever reason and instead of calling methods on the CriteriaBuilder object they were calling them on the AccountController object.

Here’s the solution:

def crit_a = {crit ->
  crit.accountAction { 
    ne("name", "None")
  }
}

def crit_b = {crit ->
  crit.accountAction {
    ne("name", "Renew")
  }
} 

def account_list = {crit, params ->
  def req_owner = User.get(params.id)
  if (params.sort == 'userName') {
    crit.owner {
      if (req_owner) {eq("id", req_owner.id)}
      order("lastName", params.order ? params.order : "asc")
      order("firstName", params.order ? params.order : "asc")
    }
  } else {
    if (req_owner) {crit.owner {eq("id", req_owner.id)}}
    crit.order(params.sort ? params.sort : "id", params.order ? params.order : "asc")
  }
}

def list = {
  params.max = Math.min( params.max ? params.max.toInteger() : 20,  100)
  params.offset = params.offset ? params.offset.toInteger() : 0

  def critBuilder = Account.createCriteria()
  def crit_closure = {
    account_list.curry(critBuilder, params)()
    crit_a.curry(critBuilder)()
    crit_b.curry(critBuilder)()
  }

  def results = critBuilder.list(max:params.max, offset:params.offset, crit_closure)
  [ accountInstanceList: results, accountInstanceTotal: results.totalCount ]
}

You get a reference to the CriteriaBuilder object using Account.createCriteria(). Note that you get a CriteriaBuilder object not a Criteria object like much of the documentation suggests. You curry this to the closures you want to use, and call them inside a new closure which you pass to the CriteriaBuilder object’s “list” method. And it works! So now you can dynamically build up your criteria queries!