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?

score 3 · Accepted Answer · edited May 23 '17 at 12:06

3

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

edited May 23 '17 at 12:06

Community

1
1

answered Jan 05 '11 at 13:50

Tihom

3,384
6
36
47

score 1 · Answer 2 · edited May 23 '17 at 12:19

1

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

edited May 23 '17 at 12:19

Community

1
1

answered Jan 07 '11 at 09:50

Medrod

986
7
17

The example you listed is querying from one table (the Transaction table). In the query above, I am querying from a subquery not a table. Can you write the Criteria for this in grails? I simplied the query above, in my real app the query is more complicated and has two nested subqueries. – Tihom Jan 07 '11 at 12:35

score 0 · Answer 3 · answered Jan 05 '11 at 13:56

0

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.

answered Jan 05 '11 at 13:56

Victor Sergienko

13,115
3
57
91

count(XXX) should return number field. – Tihom Jan 05 '11 at 20:12
:D What whole dataset type you expect in Groovy? `Collection` or `Collection>`? – Victor Sergienko Jan 06 '11 at 12:25
[[]] is what I was expecting, but I would accept [[:]]. For example: [[1223, 5], [14455,3], [1111, 4]]. Each subarray is a row in the result set. – Tihom Jan 06 '11 at 22:42
If you write a full statement, starting with `select`, it will return List, try it. – Victor Sergienko Jan 08 '11 at 21:21

Querying computed fields in GORM

3 Answers3

Linked