HQL question 1 for many count ()
I am trying to write a query in HQL and I am having problems with it. It's probably not too hard, but I'm pretty terrible at query languages in general and HQL in specific ones.
Basically, there are three tables: Owners, Pets, and Toys, whose classes are as follows:
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 an HQL query that, given the Owner, returns the number of their pets that have at least 3 squeaky toys. I'm sure there should be a pretty simple HQL way to solve this problem, but find me if I know what it is.
I would also be happy to know any helpful HQL tutorials outside of the documentation (which is great, assuming it's already SQL-pro and I am not).
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 admit I didn't try it, I did it quickly.
More object oriented way (not sure about performance):
select count(pet)
from Pet pet
where pet.owner.id = :ownerId
and size(pet.toys) >= 3