Get children score through HQL

Question

I have a one-to-many mapping between the parent entity and the child objects. Now I need to find the number of children associated with each parent for the list of parents. I am trying to do this using HQL, but I'm not sure how I can get the list of parents. In addition, I do not know how I can return the object itself, and not just its identifier. My current HQL query:

select new map(parent.id as parentId, count(*) as childCount) from Parent parent left join parent.children children group by parent.id 

but this only returns an identifier and does not filter specific parents.

EDIT Based on Pascal's answer, I changed the request to

 select new map(parent as parent, count(elements(parent.children)) as childCount) from Parent parent group by parent 

This works, but is excessively slow: 30 seconds instead of 400 ms in one database.

Answer 1

I'm not 100% sure, but what about this:

 select new map(parent.id, count(elements(parent.children))) from Parent parent group by parent.id 

Answer 2

Thanks. I was looking for the number of children for a specific parent, and your messages made me move in the right direction.

If someone else needs to find the number of children for a specific parent, you can use something like this:

 Query query = this.sessionFactory.getCurrentSession().createQuery("select count(elements(parent.children)) from Parent parent where name = :name"); query.setString("name", parentName); System.out.println("count = " + query.uniqueResult());