Get children count via HQL

Question

Hi,

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 parentId, 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.

Answer 1

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

Answer 2

The query suggested above is also applicable to my need. So I applied it in my project.

select new UpdateCountDataBean(count(elements(am.actionId)) as noOfUpdates, am.pname as name) from ActivityMaster am group by am.pname

The UpdateCountDataBean is created by me while the second class i.e. ActivityMaster is POJO class.

But I am getting a NullPointerException as Below :

java.lang.NullPointerException
at org.hibernate.hql.ast.tree.MethodNode.handleElements(MethodNode.java:158)
at org.hibernate.hql.ast.tree.MethodNode.resolveCollectionProperty(MethodNode.java:109)
at org.hibernate.hql.ast.tree.CollectionFunction.resolve(CollectionFunction.java:22)
at org.hibernate.hql.ast.HqlSqlWalker.processFunction(HqlSqlWalker.java:835)
at org.hibernate.hql.antlr.HqlSqlBaseWalker.collectionFunction(HqlSqlBaseWalker.java:2558)
at org.hibernate.hql.antlr.HqlSqlBaseWalker.aggregateExpr(HqlSqlBaseWalker.java:2907)
at org.hibernate.hql.antlr.HqlSqlBaseWalker.count(HqlSqlBaseWalker.java:2483)
at org.hibernate.hql.antlr.HqlSqlBaseWalker.selectExpr(HqlSqlBaseWalker.java:1971)
at org.hibernate.hql.antlr.HqlSqlBaseWalker.aliasedSelectExpr(HqlSqlBaseWalker.java:2057)
at org.hibernate.hql.antlr.HqlSqlBaseWalker.constructor(HqlSqlBaseWalker.java:2226)
at org.hibernate.hql.antlr.HqlSqlBaseWalker.selectExpr(HqlSqlBaseWalker.java:1952)
at org.hibernate.hql.antlr.HqlSqlBaseWalker.selectExprList(HqlSqlBaseWalker.java:1825)
at org.hibernate.hql.antlr.HqlSqlBaseWalker.selectClause(HqlSqlBaseWalker.java:1394)
at org.hibernate.hql.antlr.HqlSqlBaseWalker.query(HqlSqlBaseWalker.java:553)
at org.hibernate.hql.antlr.HqlSqlBaseWalker.selectStatement(HqlSqlBaseWalker.java:281)
at org.hibernate.hql.antlr.HqlSqlBaseWalker.statement(HqlSqlBaseWalker.java:229)
at org.hibernate.hql.ast.QueryTranslatorImpl.analyze(QueryTranslatorImpl.java:228)
at org.hibernate.hql.ast.QueryTranslatorImpl.doCompile(QueryTranslatorImpl.java:160)
at org.hibernate.hql.ast.QueryTranslatorImpl.compile(QueryTranslatorImpl.java:111)
at org.hibernate.engine.query.HQLQueryPlan.<init>(HQLQueryPlan.java:77)
at org.hibernate.engine.query.HQLQueryPlan.<init>(HQLQueryPlan.java:56)
at org.hibernate.engine.query.QueryPlanCache.getHQLQueryPlan(QueryPlanCache.java:72)
at org.hibernate.impl.AbstractSessionImpl.getHQLQueryPlan(AbstractSessionImpl.java:133)
at org.hibernate.impl.AbstractSessionImpl.createQuery(AbstractSessionImpl.java:112)
at org.hibernate.impl.SessionImpl.createQuery(SessionImpl.java:1623)

PLZ PLZ HELP ME OUT.....