Querying computed fields in GORM

I am trying to query the following HQL using GORM:

MailMessage.executeQuery("toId, count(toId) from (SELECT  toId, threadId FROM MailMessage as m WHERE receiveStatus = 'u' GROUP BY threadId, toId) as x group by x.toId")

The problem is that count(toId) is a computed field doesn't exist in MailMessage and that I am using a subquery.

I get the following error: java.lang.IllegalArgumentException: node to traverse cannot be 开发者_C百科null! Ideally, I would like to use a generic executeQuery which will return data of anytype. Is there such a thing?

---

I am answering my own question. The only way to do this is to execute raw SQL and not use HQL. Unfortunately, there is no way that I figured out how to execute a complicated query with a subquery and computed field.

I used the example here: Grails query not using GORM

---

You can solve this also with criteria builder. Using groupProperty and countDistinct in Grails Criteria

Edith: Ok you have to mix hibernate criteria into the criteria builder. For subqueries you have to use org.hibernate.criterion.DetachedCriteria.

At first you have to create the subquery:

 DetachedCriteria avgWeight = DetachedCriteria.forClass(Cat.class)
.setProjection( Property.forName("weight").avg() );

If I'm right than you can use it into the criteria builder:

Cat.withCriteria{
   and{Subqueries.geAll("weight", weights)}
}

---

It's unclear - what data type are you expecting to get?

If you want just a datatset - list of field value lists, you need to use full HQL "select" syntax, executeQuery has some examples of.

It looked like you wished to get a collection of MailMessage-s with an extra countToId field. That's not doable straightforward way, unless you could add dummy field to MailMessage and write an 'select' expression for it.