Clever Geek Handbook

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).

+2


source to share


2 answers


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.

+1


source


More object oriented way (not sure about performance):



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


source






More articles:


All Articles