Querying computed fields in GORM

Question

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 null! Ideally, I would like to use a generic executeQuery which will return data of anytype. Is there such a thing?

Answer 1

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

Answer 2

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