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()

session.flush()
session.clear()
}

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

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 {
property('ship')
}
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.

5 comments:

meier said...

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

Tom

atmcclain 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 http://jira.codehaus.org/browse/GRAILS-7087

Sergey Ponomarev said...

How can I do same thing with `where` criteria

Carolyn Widmer said...

Good luck with your blog! http://www.assignment-help-sydney.com/aussiewriter-com-review/