Querying associations in Grails with HQL, Criteria and HibernateCriteriaBuilder

A thing I was playing with today was many-to-many relationships in Grails to create a Tag Cloud. To create a Tag Cloud, I must have a set of key/value pairs, each with a label and a value of the label, which could look like this:

['Java': 5, 'Grails': 16, 'Groovy': 12]

But to query this, I need to query a many to many relationship and produce the above result. This blog will describe how to do this with HQL, Criteria and the HibernateCriteriaBuilder.

Domain

My current domain looks like this:

(This picture is unfortunately missing a Tag attribute in the Snippet class).

As you can see, a Snippet can have multiple Tags, while a Tag can belong to multiple Snippets. How can we query this?

Options

We have three options in this case

  • HQL
  • Hibernate Criteria
  • Hibernate Criteria using the HibernateCriteriaBuilder

HQL
The HQL version is not so hard (and neither are the other ones), but we have to start somewhere, so let's start with this one.

To start at the end: the query looks like this:

select tag.text, count(snippet.id)
from Tag as tag
inner join tag.snippets as snippet
group by tag.text

To test this code, I find it easiest to start up the Grails console:
grails console
and run the HQL there. You can do so by obtaining the SessionFactory from the ApplicationContext, which is done by accessing the implicit 'ctx' variable in the console. The complete code looks like this:

def sessionFactory = ctx.sessionFactory
def session = sessionFactory.getCurrentSession()
def query = session.createQuery("select tag.text, count(snippet.id) from Tag as tag inner join tag.snippets as snippet group by tag.text")
def results = query.list()

Which, in my case, returns this (I have two snippets, one with 3 tags and one with 1 tag, which is a duplicate tag of the first snippet):

[["groovy", 1], ["io", 2], ["testing", 1]]

Hibernate Criteria
Since Grails is built on Groovy/Java, Hibernate and Spring, it's easy to use that power while coming up with a solution to your problem. If you write Java code all day, and writing Hibernate Criteria is something you do daily, this won't be a problem at all. Just copy and paste your existing Java code into Grails, and you'll produce something like this:

def sessionFactory = ctx.sessionFactory
def session = sessionFactory.getCurrentSession()
List results = session.createCriteria(Tag.class)
  .setProjection ( Projections.projectionList()
     .add (Projections.groupProperty("text")) 
     .add (Projections.rowCount() ) )
  .createCriteria("snippets")
  .list()

Hibernate Criteria using the HibernateCriteriaBuilder
If you, however feel that the above is lacking some style, you might want to check out the HibernateCriteriaBuilder, which is a DSL for building Hibernate Criteria.

When using the DSL, we first need to retrieve the HibernateCriteriaBuilder, which can be obtained from any domain class. After that, you'll have the full power of Hibernate contained in an easy, readable DSL.

def c = Tag.createCriteria()
def results = c.list {
   projections {
      groupProperty("text")
      rowCount()
   }
   snippets { }
}          

Conclusion

As you can see, Grails provides a lot of options to query your domain. Whether you prefer HQL, the safe and sound Criteria, or the new HibernateCriteriaBuilder, the choice is up to you. By leveraging the power of existing frameworks, Grails provides an easy and powerful way to quickly develop an application with a minimal learning curve!

Comments (7)

  1. Jan - Reply

    June 18, 2008 at 12:32 am

    For grailstutorials.com I used 'act as taggable' plugin.
    Inner implementation of this plugin will actually create two tables.
    Tag table
    Tagging table with important columns tagId, taggableType (actually class name of tagged domain object).

    I prefer this approach because I don't need tag field in my domain classes but I can tag them.

    Another advantage is that querying tag cloud from the database is easier at least from my point of view.

    Tagging.executeQuery("select distinct t.tag.name, count(t.tag.id) from Tagging t where t.taggableType=? group by t.tag.id"

    Jan

  2. puran - Reply

    September 16, 2008 at 9:35 pm

    i am not sure where is that ctx coming from??
    i get not defined exception, actually i am trying to use that in my tests.

  3. Erik Pragt - Reply

    September 17, 2008 at 9:55 pm

    Hi Puran,

    Like I described, it's an implicit variable in the console. If you're running tests, and your tests are 'normal' unit tests (so no integration) than my guess is that they are not there.

    Have you tried the console?

  4. Rogério Carrasqueira - Reply

    January 3, 2009 at 1:46 am

    Hi, how can you do to render the results on a gsp file, for example using ?

    Thanks,

    Rogério Carrasqueira

  5. Pam - Reply

    January 27, 2009 at 10:16 pm

    In a grails controller, you can also use DomanObject.executeQuery()

    Documented here:
    http://grails.org/doc/1.0.x/ref/Domain%20Classes/executeQuery.html

  6. Jeremy Leipzig - Reply

    April 15, 2009 at 9:45 pm

    how do you add multiple distinct fields?

Add a Comment