Querying on property of a hasMany relationship

Question

I have the following association in a domain I'm working on (not actually pirates unfortunately). A ship has many pirates. I want to be able to find all ships that have a captain and do not have any land lubbers on the crew.

class Pirate {

  String name
  Rank rank

  enum Rank {
    CAPTAIN,
    DECK_HAND,
    LAND_LUBBER
  }

}

class Ship {

  static hasmany = [crew:Pirate]

}

I'm trying to use Hibernate criteria as in the long run I'd like this to be a named query that I can chain with other queries. Here is the criteria I'm using.

def pirateShips = Ship.withCriteria {
  crew {
    eq('rank', Pirate.Rank.CAPTAIN)
    not {
      eq('rank', Pirate.Rank.LAND_LUBBER)
    }
  }
}

When I create the following entities I don't get the expected results.

def blackbeard = new Pirate(name:'Blackbeard', rank:Pirate.Rank.CAPTAIN).save()
def deckHand = new Pirate(name:'Deck Hand', rank:Pirate.Rank.DECK_HAND).save()
def landLubber = new Pirate(name:'Land Lubber', rank:Pirate.Rank.LAND_LUBBER).save()

def ship = new Ship(name:'Ship 1', crew:[blackbeard]).save()
def infiltrator = new Ship(name:'Ship 2', crew:[blackbeard, landLubber]).save()
def normalShip = new Ship(name:'Ship 3', crew:[blackbeard, deckHand]).save(flush:true)

Running the afore mentioned query results in all 3 ships being returned when I would expect only Ship 1 and Ship 3 to be returned (Ship 2 has a pesky land lubber which is of no use to me).

Is there a way to use the criteria API to generate such a query? If not how would I go about writing a query in HQL?

Answer 1

I think you'll have to use HQL for this but I did run across the following article that might give you another idea of how to solve the problem.

http://adhockery.blogspot.com/2009/04/associations-and-criteria-queries.html

Answer 2

The reason that your query isn't working is mostly because of the nature of SQL joins. It'll return a ship if any individual row matches your condition. There are rows in the join that are captains but not land lubbers (i.e. every blackbeard row), so you're getting all the ships back. Written that way, the criteria isn't looking at all the crew members, just each crew member individually.

There are a few different ways to solve this problem. One is to use a subselect query. It might be possible to write this in a criteria, but I'm not as familiar with them as I favor HQL as it looks a lot more like SQL, which I'm comfortable with.

Here's an example HQL query that returns the expected ships 1 and 3:

def blackbeard = Pirate.buildLazy(name: 'Blackbeard', rank: Pirate.Rank.CAPTAIN)
def deckHand = Pirate.buildLazy(name: 'Deck Hand', rank: Pirate.Rank.DECK_HAND)
def landLubber = Pirate.buildLazy(name: 'Land Lubber', rank: Pirate.Rank.LAND_LUBBER)

def ship = Ship.buildLazy(name: 'Ship 1').with { crew = [blackbeard] }
def infiltrator = Ship.buildLazy(name: 'Ship 2').with { crew = [blackbeard, landLubber] }
def normalShip = Ship.buildLazy(name: 'Ship 3').with { crew = [blackbeard, deckHand] }

def pirateShips = Ship.executeQuery( """
    select s from Ship s 
    join s.crew as p 
    where p.rank = :captain 
    and s not in 
        (select s1 from Ship s1 
         join s1.crew as p1 
         where p1.rank = :landLubber)
""", 
[captain: Pirate.Rank.CAPTAIN, landLubber: Pirate.Rank.LAND_LUBBER] )
assert( ["Ship 1", "Ship 3"] == pirateShips.name.sort() )

(I also used the build-test-data plugin to lazy build the instances rather than newing them up as it made the script easier to run multiple times without restarting the grails console all the time).

In this query, I'm finding all of the ships with captains and from that set removing all of the ships that have land lubbers.