Fork me on GitHub

1 Apr 2009

Querying By Association With Grails Criteria

A common requirement is to select all instances of a domain class where one of its many-to-many associations contains a particular instance of another domain class. Consider these domain classes where a Ship has a crew composed of Pirates and any particular Pirate can be a part of the crew of several Ships:

class Ship {
static hasMany = [crew: CrewMember]
String name

class CrewMember {
static belongsTo = [ship: Ship]
Pirate pirate

class Pirate {
String name

How can we use a criteria query to find all the Ship instances where a particular Pirate is a member of the crew? You might think it's pretty simple, surely:

Ship.withCriteria {
crew {
eq('pirate', blackbeard)

However, this has a curious side-effect. For example:

Ship.withSession {session ->
blackbeard = new Pirate(name: 'Blackbeard')
jack = new Pirate(name: 'Calico Jack')
bart = new Pirate(name: 'Black Bart')
[blackbeard, jack, bart]*.save()

def ship1 = new Ship(name: "Queen Anne's Revenge")
ship1.addToCrew new CrewMember(pirate: blackbeard)
ship1.addToCrew new CrewMember(pirate: jack)

def ship2 = new Ship(name: "Royal Fortune")
ship2.addToCrew new CrewMember(pirate: blackbeard)
ship2.addToCrew new CrewMember(pirate: bart)

def ship3 = new Ship(name: "The Treasure")
ship3.addToCrew new CrewMember(pirate: jack)
ship3.addToCrew new CrewMember(pirate: bart)

[ship1, ship2, ship3]*.save()


def blackbeardsShips = Ship.withCriteria {
crew {
eq('pirate', blackbeard)
blackbeardsShips.each {
println "$ ${', ')}"

You might expect the output to be:

Queen Anne's Revenge: Blackbeard, Calico Jack
Royal Fortune: Blackbeard, Black Bart

but it is actually:

Queen Anne's Revenge: Blackbeard
Royal Fortune: Blackbeard

The criteria query has restricted the content of the associations to only the ones matching the eq('pirate', blackbeard) criterion. This is quite a problem as it may well not be immediately obvious that it's happened and even using ships*.refresh() (which would be a horrible hack, especially if we were likely to get more than a couple of results) doesn't seem to restore the missing entries. I don't think there is any criterion that does a 'contains' type match for a persistent collection. My guess is this problem isn't a Grails thing but a Hibernate thing - Grails' HibernateCriteriaBuilder is a very thin layer over Hibernate itself.

In this example there is an alternative as the association is bi-directional. We can query from the other side of the association:

def blackbeardsShips = CrewMember.withCriteria {
projections {
eq('pirate', blackbeard)

If the association wasn't bidirectional this wouldn't be possible and we'd probably have to resort to using HQL to make the query exhibit the correct behaviour.


Unknown said...

I was facing this issue today. Unfortunately, I don't have a bidiractional association. Could you provide a HQL statement for the example?


Unknown said...

I, too, would benefit from an HQL example here. The post describes the issue with the criteria query very clearly, but I'm also stuck without a bidirectional association and have yet to figure out the HQL. Thanks!

z said...

Here some usefull info

stokito said...

How can I do same thing with `where` criteria

Anonymous said...

Good luck with your blog!

reena said...



vijay said...



jasonbob said...

supreme hoodie
kyrie 5 shoes
supreme hoodie
kobe byrant shoes
golden goose
moncler jackets
supreme clothing
nike sb

vijay said...

This configuration can also be used for devices like Router, Modem etc. and they are all having the initial IP address


Unknown said...

Full Article this link replica bags china next page Loewe Dolabuy

Unknown said...

informative post Dolabuy Hermes website link Ysl replica bags visit this site right here Louis Vuitton replica Bags