Grails – query or criteria against a string/value pairs map property

April 21, 2014 at 10:35

Grails has a feature that I find sometimes useful: mapping a simple map of string/value pairs using GORM. That is, given a domain class, if you create a property whose type is Map, and the name of the property is not the name of an association (hasMany, etc), this property will act as a String to String map than will be automatically persisted and retrieved by GORM. You only need to care about filling the map up as you would do with a normal map (keeping in mind, of course, that both the keys and values must be strings).

But, if you carefully read what I wrote, you might be wondering why I just said that GORM will take care of persisting and retrieving. What about querying? Well, like many other areas in Grails, the documentation only covers the Grails specifics, leaving the Hibernate / Spring / other components out for you to find 🙂 . I guess is hard to find a balance on that topic, but in any case, I couldn’t find how to query a domain class using the contents of its key/value pairs properties.

Immediately after after posting a question in Stackoverflow, I discovered how to do it with HQL. Let’s use the classic Author domain class as an example, just as the Grails documentation does:

class Author {
   Map bookTitlesByIsbn = [:]// map of ISBN:book names
}

Given an ISBN and a book title fragment, you could query the Author domain class like the following:

String isbn = params.isbn, partialTitle = params.title
Author.executeQuery("from Author a where a.books['${isbn}'] like '%${partialTitle}%'")

It probably doesn’t make much sense to have the ISBN and not the title, as both are “user input”, but for me, the common usage of this type of properties would be to store the entries of an enum as keys, so latter you could query by value for those entries.

I created a demo app that illustrates the example, and for the lazies who don’t want to download and run it 😀 , I deployed it in Heroku.

Sadly, I couldn’t find how to do the same with Hibernate Criterias, which I usually prefer over HQL: the former is based on closures and methods (i.e it has a DSL), so the readibility and composibility is way better. But still, I found pretty neat that the map syntax worked in HQL the way it does 🙂 .