Grails: Querying across associations

Another nerdy post. Grails is a pretty good framework. I’m a python guy, so I prefer Django, but when forced to use Java-like things Grails is better than the alternative. However, it’s still young. Django and Grails are both currently on their 1.1.1 releases, but Django is much more mature for its age.

In Django it’s really easy to query across related objects (they’re called “related objects” in Django and “associations” in Grails). Grails is still struggling with this. (Grails is also struggling with good, in-depth documentation, but that’s not the purpose of this post.)

After much searching all I could find was some forum posts by the project lead of Grails, Graeme Rocher, from 2007 saying that nested associations aren’t currently (as of 2007) supported.

Nested Associations: Suppose I have 3 classes: Person, Family, and Country. Suppose the classes are designed such that each person belongs to a family and each family is linked to an origin country. Now suppose you want to get a list of all persons whose family is from England. Persons don’t have a direct link to that information, so you’d need to hop through the family to get to the country.

Based on the current setup you’d expect to be able to do something like:

Person.withCriteria {
family {
country {
eq("name", "England")
}
}
}

And you can. So for anyone searching for how to do this and finding that old post from 2007 saying you can’t: it’s wrong. You can.

But now let’s pickup where I left off in my previous post with separating out query pieces for re-usability and adherence to the DRY principle.

We need to build a criteria object specifically and separate out the criteria to a separate closure:

def someView = {
def critBuilder = Person.createCriteria()
def critClosure = { filterByEngland.curry(critBuilder)() }
def results = critBuilder.list(max:params.max, offset:params.offset, critClosure)
def totalCount = results.totalCount
}

def filterByEngland = {critBuilder ->
critBuilder.family {
critBuilder.country {
eq("name", "England")
}
}
}

And now we can combine that with other pieces of modularized code. I have my queries broken up so that I can easy sort using different functions based on what kind of output the data is going to be used in. So you can have something like this:

def someView = {
def critBuilder = Person.createCriteria()
def critClosure = {
filterByEngland.curry(critBuilder)()
sortForCSV.curry(critBuilder)()
// sortForXML.curry(critBuilder)()
}
def results = critBuilder.list(max:params.max, offset:params.offset, critClosure)
def totalCount = results.totalCount
[results: results, totalCount: totalCount]
}

def filterByEngland = {critBuilder ->
critBuilder.family {
critBuilder.country {
eq("name", "England")
}
}
}

def sortForCSV = {critBuilder ->
critBuilder.order("lastName", "asc")
critBuilder.order("firstName", "asc")
critBuilder.order("age", "asc")
}

def sortForXML = {critBuilder ->
critBuilder.family {
critBuilder.country {
order("name", "asc")
}
order("id", "asc")
}
}


Since this nested association querying isn’t documented anywhere (that I could find) and the only mention is that it _doesn’t_ work, it was a pain in the butt figuring it out.

Other gripes with Grails. I can’t define a relation to another class unless it is based on the primary_key of the classes. A less-than-usual case for sure, but there really isn’t any good reason to disallow such a situation.

And now my feet hurt.

I’ve spent my afternoon scrubbing mildew out of windowsills. (How is it suddenly there in such large, disgusting amounts, when it was previously not there at all?) Blergh. The worst was pulling back the blinds in front of the patio door. [shudder]

If anybody has any magical tips for preventing/easily getting rid of* said pestilence, I’d love to hear them. This is something we’ve not had to deal with before. Do not like.

*I know I can use bleach, but I’m scared of getting it on the carpet (some of the mildew is around the patio door) or something and ruining the carpet. And I’ve heard that rolled up towels on the windowsills will catch the water and help, but I’m not sure how much that will help (I haven’t tried it) and I don’t like the idea of how it would look. I may be willing to at least try it, though, if there aren’t any better options. Some of these are porous surfaces, which makes them rather difficult to clean. Today’s method was just a wet rag and Fantastik, which worked okay, but there’s gotta be something better. 🙁

Life at Work

I’ve been working some small projects to prototype using Groovy and Grails as our new framework. It’s been fun to focus on a small project with enough time to actually polish it up nicely. The Grails framework lets us actually focus on functionality rather than minutiae that make the system function. So we’ve been putting together a nice little system which uses Ajax to provide a quick and clean interface.

Halloween

Ok, we’ve been lazy about Halloween. Ok, Ok, blogging Halloween was _my_ job, so I’ve been lazy.

Anyway. The week before Halloween we went out to a farm in Lathrop which was supposed to have a pumpkin patch which would allow you to pick a pumpkin straight from the vine (unlike the “pumpkin patch” in Livermore which trucks them in and lines them up). So we headed out there for the pumpkin patch, and corn mazes, aand pumpkin CANNONS!

It turned out that the pumpkin was a bit lame. It looks like it had been an actual patch, but it looked like they had done their best to destroy any vines that had been there.

But we did still go through a corn maze:DSCN4427Jess didn’t particularly enjoy the corn maze. She felt that if she wanted to be lost, she could just go walk around town for 10 minutes. After wandering for a bit we picked a wall and followed it all around until we got out. There were 3 mazes, but we only did one.

Then we headed over to the CANNONS! You get a bucket of small pumpkins that you can load one-at-a-time into your compressed-air cannon. It was getting dark and the lighting was crappy so the pictures aren’t very good. In this one you can see one of the targets you get to aim at, a giant jack o’ lantern. There was a good 6 feet of piled pumpkin remains at its base:DSCN4430And here’s Jess:DSCN4433
So that was fun. The pumpkin cannons were definitely worth it.

We bought a pumpkin from the grocery store because it was cheaper than the “pumpkin patch”. We didn’t get around to carving it until Halloween. I began working on a top-secret pumpkin exo-skeleton project:DSCN4442It didn’t work out very well. But the jack o’ lantern was pretty decent:DSCN4444We sat about waiting for trick-or-treaters (with a big bowl of candy) but were rather disappointed in that regard. We had one family with 3 small kids come by. That was it. So now we have a bunch of candy to eat (and Jess got more for 75% off at CVS after Halloween). Since the door wasn’t busy Dr. Horrible walked down to Panda Express for traditional Halloween cuisine (the new SweetFire Chicken Breast is awesome, I can’t eat Orange Chicken anymore):DSCN4450

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!