Grails: Dynamically building criteria queries

October 26, 2009 10:53 am

** 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!

Reading Comprehension

October 17, 2009 10:36 am

Both of our laptops suffered unfortunate deaths over the last few months. Well, mine died way back in the spring, and Jess' was suffering from a degenerative condition resulting in it being mostly inoperable. I held on to my laptop because most of the pieces were still good, so it seemed a shame to just get rid of them. Now that both the computers were dead, I decided it was time to get rid of the pieces. So I dismantled all the usable stuff (memory, batteries, optical drives, whatever). I looked on eBay and decided it wasn't worth trying to sell any of it so I listed it for free on Craigslist. The posting starts out like this:

My laptop died a couple of months ago, and my wife's died last month. So I have a couple handfuls of parts to get rid of. They don't seem to be worth trying to sell on ebay, so I'm offering them up for free here.

Apparently, some of the people missed the possessive on "wife". I received these replies:

From Keith:

So sorry to hear about your wife....but life must go on....

From Lorimer (in the form of an eCard with a picture of flowers!):

I'm so sorry to hear that your wife is in a better place now. I hope she had a good life. My sincere condolence.

As for the laptop parts that you're giving away on Craigslist, if it's okay with you, I would like to take them all off your hand in light of relieving some of the burdens off your shoulder

FYI: I Did

October 14, 2009 3:37 pm

We were watching some West Wing last night while eating dinner. Jess got up and walked to the kitchen. I was still sitting on the couch when it felt like someone had walked into the end of the couch—a definitive bump, and small, but noticeable, jostling. I thought it was weird and mentioned it to Jess. She said something along the lines of me being crazy. Well, this morning I happened to overhear something about an earthquake. Turns out that at 8:30 p.m. last night (which would have been right around when the couch event occurred) there was a magnitude 3.7 earthquake right next door in Pleasanton. According to the Contra Costa Times people reported feeling the earthquake up to 60 miles away. Since we are less than 10 miles from the epicenter, I don't think it is unreasonable to assume that what occurred last night was the earthquake.

epicenter

In more important news...

3:37 pm

...I'm officially a Dickerson! The lady from Social Security called me last week while we were at the airport to tell me that she had finally managed to procure a certificate number for my birth certificate, so I could come on back to the office. So I went down this afternoon to get this name changing business taken care of. Of course, it didn't go exactly smoothly. She needed my ID, and the only real one I have is now expired. I gave her my temporary CA license, which she had to go ask about. Her supervisor's original answer was no, she couldn't use that, but when she explained who I was (I'm known down there), she said to go ahead and use it. Which is good, because I can't get my real CA license until SS changes my name, so that would have been a handy little disaster. But it's done, it all went through, I have an official receipt, and should have my new card in 2 weeks. She proclaimed me "Mrs. Dickerson" before I left, and I signed the paperwork "Jessica H. Dickerson," 'cuz that's who I am now.

And now, a photographic representation of how I feel about this:0626Fireworks

Next stop: the DMV. When I tried to think of a photo that would represent my feelings on returning there, all I could think of was slimy disgusting mold, and google's image search finds some really nasty things. I'm not posting them on here. You're welcome.