HQL 1 to many count() question

Question

I'm trying to write a query in HQL, and I'm having some trouble with it. It's probably not too difficult, but I'm pretty awful at query languages in general and HQL in specific.

Basically, there are three tables, Owners, Pets, and Toys, whose classes look like this:

public class Owner {
 long ownerId;
 List<Pet> pets;
}
public class Pet {
 Owner myOwner;
 List<Toy> toys;
}
public class Toy {
  Pet petThatOwnsThisToy;
  boolean isSqueaky;
}

I'm looking for a HQL query that, given an Owner, returns the number of their pets that have at least 3 squeaky toys. I'm sure there's gotta be a pretty simple HQL way to solve this, but search me if I know what it is.

I'd also be happy to discover any helpful HQL tutorials beyond the documentation (which is excellent, assuming one is already an SQL pro, which I'm not).

Answer 1

What about ?

select count(pet)
from Pet pet 
join pet.myOwner owner
where owner.id = :ownerId
and pet.id in (
   select pet.id 
   from Toys toy
   join toy.petThatOwnsThisToy pet
   group by pet.id
   having count(pet.id) >= 3
)

I must confess I haven't tried it, I made it up quickly.

Answer 2

A more object-oriented way (not sure about performance though) :

select count(pet)
from Pet pet 
where pet.owner.id = :ownerId
and size(pet.toys) >= 3