Fork me on GitHub

13 Jun 2009

Querying by Association Redux

A couple of months back I posted about the problems with criteria queries where you want results with a collection property that contains some value.

Last night I was reading through my new copy of Glen Smith and Peter Ledbrook's Grails In Action and my eye was caught by a particular example where they use aliases in a criteria query. Sure enough a quick test this morning confirms that this is the solution to the problem.

To recap. If I have these domain classes:

class Pirate {
String name
static belongsTo = Ship
static hasMany = [ships: Ship]
}

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

and I want to query for all the Ships containing a particular Pirate I would probably try to do this:

def ships = Ship.withCriteria {
crew {
eq("name", "Blackbeard")
}
}

Unfortunately, while I end up with the correct Ship instances, each of their crew collections only contains the item(s) that matched the criteria regardless of any others that may actually exist.

Using an alias we can do this type of query without resorting to HQL:

def ships = Ship.withCriteria {
createAlias("crew", "c")
eq("c.name", "Blackbeard")
}

A complete test case that proves the point using the domain classes above is:

class PirateTests extends GroovyTestCase {

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

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

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

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

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

session.flush()
session.clear()
}
}

void testQueryByAssoc() {
def ships = Ship.withCriteria {
crew {
eq("name", "Blackbeard")
}
order("name", "asc")
}

assertEquals 2, ships.size()
assertEquals "Queen Anne's Revenge, Royal Fortune", ships.name.join(", ")
assertEquals "Expected 2 crew but found ${ships[0].crew.name}", 2, ships[0].crew.size()
assertEquals "Expected 2 crew but found ${ships[1].crew.name}", 2, ships[1].crew.size()
}

void testQueryByAssocUsingAlias() {
def ships = Ship.withCriteria {
createAlias("crew", "c")
eq("c.name", "Blackbeard")
order("name", "asc")
}

assertEquals 2, ships.size()
assertEquals "Queen Anne's Revenge, Royal Fortune", ships.name.join(", ")
assertEquals "Expected 2 crew but found ${ships[0].crew.name}", 2, ships[0].crew.size()
assertEquals "Expected 2 crew but found ${ships[1].crew.name}", 2, ships[1].crew.size()
}
}

The first test fails as each Ship returned by the query only has Blackbeard in the crew despite the fact that they were created and saved with 2 crew members each. The second test retrieves the correct results.

Logging out the SQL generated by the Hibernate queries shows the difference in what it's doing under the hood. The first (incorrect) query is:

select * from ship
left outer join ship_crew on ship.id = ship_crew.ship_id
left outer join pirate on ship_crew.pirate_id = pirate.id
where pirate.name = ?
order by ship.name asc

The second (correct) query is:

select * from ship
inner join ship_crew on ship.id = ship_crew.ship_id
inner join pirate on ship_crew.pirate_id = pirate.id
where pirate.name = ?
order by ship.name asc

So the first query is using left outer joins and the second is using inner joins. Running those queries directly in SQuirreL returns basically the same result set so the discrepancy must be in how Hibernate treats the results. It seems Hibernate has populated the Ship instance and its crew collection after the first query and therefore considers the collection to be initialized, although actually the data in the result set was not the complete collection. In contrast, after the second query you can see the individual SQL select statements as the lazy-loading fires when the assertions are done, so Hibernate obviously only populated the root Ship instance from the query results and is treating the collection property as uninitialized.

10 comments:

anschoewe said...

This is fascinating. I noticed this issue a while back and couldn't figure out what was going on. I would like to know if there is a way to use a a criteria search that returns the ships with their pirates. Your second solution only works because of lazy loading. If we wanted to be more efficient, is there a way to retrieve the ship and all of it's pirates in one query? Do we have to resort of HQL?

I suppose you can set the fetch mode of the relationship to 'eager', but we may not want this in all situations.

Andrew

Rob said...

You can specify the join type on the createAlias call but INNER_JOIN is the default which works as per the example, LEFT_JOIN reintroduces the bug and FULL_JOIN fails with invalid SQL!

GotchaMaus said...

thanks for the criteria + alias sample ..
helped alot ..

Burt said...

Thank you very much for the explanation and example. I've been working on this for (at least) a day, and couldn't figure out what was going on until I read your excellent explanation and perfect example.

You really helped.
Thanks very much!
Burt Prior

johnrellis said...

Almost a year on and this post is still saving bacon :) This was one hard bug to track down! Thanks again robert! Now to understand aliases... don't worry I have a copy of Grails in Action right here :)

Jacques said...

Thanks Rob, your example was a real lifesaver, I've been struggling to query multiple associations, since pretty much all of the grails documentation only show examples of queries on a single domain object.

Cheers
Jacques

Fab said...

There is a Grails defect for this:
http://jira.codehaus.org/browse/GRAILS-7087

justinbroughton said...

This works in the simple case, but not in the more complex cases with ands/ors e.g.

def ships = Ship.withCriteria {
or {
eq('name', 'Some Random Ship')
createAlias("crew", "c")
eq("c.name", "Blackbeard")
}
}

I have tried putting the createAlias in a variety of places.

My actual failing criteria looks like this:

{
isNull('endedAt')
or {
eq('invitationKind', 'OPEN')
and {
eq('invitationKind', 'GROUP')
createAlias('groups', 'g')
'in'('g.id', visibleGroupIds)
}
}
}

and it returns nothing, not even the records with invitationKind 'OPEN'.

Using the "groups { 'in'(..." works albeit returning objects with a subset of the groups.

Any ideas?

DB said...

Holly mother of Jesus! I just spent 10 hours over this ... piece of work!

Thank you for so clearly describing the problem that matches 100% to mine, and providing a clear and simple workaround!

Many, many thanks.

Dmitry

DB said...

@justinbroughton I ran into your problem as well and was able to solve it with setting FULL_JOIN on the alias (third parameter).

If you are using grails it looks like this:

createAlias("tasks", "t", Criteria.FULL_JOIN)