开发者

Get children count via HQL

开发者 https://www.devze.com 2022-12-18 16:33 出处:网络
I have a one-to-many mapping between a parent entity and child entities. Now I need to find the number of children associated with each parent for a list of parents. I am trying to do this with HQL bu

I have a one-to-many mapping between a parent entity and child entities. Now I need to find the number of children associated with each parent for a list of parents. I am trying to do this with HQL but I am not sure how I can get the list of parents in there. Also, I don't know how I can return the entity itself and not just its ID. My current HQL query is:

select new map(parent.id as pa开发者_开发百科rentId, count(*) as childCount) 
from Parent parent left join parent.children children
group by parent.id

but this only returns the ID and does not filter on specific parents.

EDIT Based on Pascal's answer I have modified the query to

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

That does work, but is prohibitively slow: 30 seconds instead of 400 ms on the same database.


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

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


Thank you. I was looking for the number of children for a particular parent and your posts got me heading in the right direction.

In case anyone else needs to find the number of children for a particular parent, you could 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());
0

精彩评论

暂无评论...
验证码 换一张
取 消