Blog

Querying associations in Grails with HQL, Criteria and HibernateCriteriaBuilder

04 Jun, 2008

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!

guest
7 Comments
Oldest
Newest Most Voted
Inline Feedbacks
View all comments
Jan
Jan
14 years ago

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

puran
13 years ago

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.

Erik Pragt
Erik Pragt
13 years ago

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?

Rogério Carrasqueira
Rogério Carrasqueira
13 years ago

Hi, how can you do to render the results on a gsp file, for example using ?
Thanks,
Rogério Carrasqueira

Pam
Pam
13 years ago

In a grails controller, you can also use DomanObject.executeQuery()
Documented here:
http://grails.org/doc/1.0.x/ref/Domain%20Classes/executeQuery.html

Jeremy Leipzig
13 years ago

how do you add multiple distinct fields?

Explore related posts