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.