HQL Nested Aggregate Functions

[Landon Cheek]

I am having a very difficult time trying to figure out how to use nested
aggregate functions in HQL.

Let me give some domain class context:

Person {
static hasMany = [events: Event]
}

Event {
static belongsTo = [person: Person]
Date timestamp
}

Specifically, what I'm trying to do in my HQL query is get the average
number of events per day, per person.

Here's the HQL query I initially tried to use:

select avg ( ( select count(*) from Event event where event.person = person
group by event.person, day(event.timestamp), month(event.timestamp),
year(event.timestamp) ) ), person
from Person person
group by person

The subquery to get the event counts (which I use for the average) seems to
be what is giving me grief. The error I get when I try and execute the query
is "org.hibernate.hql.ast.QuerySyntaxException: unexpected AST node: query",
which makes sense because I'm fairly certain that actually is invalid
syntax. My issue is that there doesn't seem to be any other way to construct
the query to get out what I want, at least not in HQL.

In SQL, what I would normally do is put the subquery in the 'from' clause,
but HQL specifically doesn't allow this for some reason that is not
fathomable to me.

I'm at a loss. Any help would be greatly appreciated.

--
View this message in context: http://grails.1312388.n4.nabble.com/HQL-Nested-Aggregate-Functions-tp4629813.html
Sent from the Grails - user mailing list archive at Nabble.com.

---------------------------------------------------------------------
To unsubscribe from this list, please visit:

http://xircles.codehaus.org/manage_email

[Samuel Gendler]

Post by Landon Cheek
In SQL, what I would normally do is put the subquery in the 'from' clause,
but HQL specifically doesn't allow this for some reason that is not
fathomable to me.
I'm at a loss. Any help would be greatly appreciated.

Given that you are selecting a raw value rather than an object from your
domain model, why not just execute a plain sql query instead and eliminate
the difficulty? There may be an HQL solution to your issue, but given the
nature of what you are doing, there doesn't seem to be much reason to
prefer hql over sql for this particular query. Yes, you'll have to load
the list of persons separately, but since you don't appear to be filtering
the list, you can load them all into a map with one hql query, and select
the avg and id in a sql query, merging the two really easily via map lookup
while iterating over the result. It's not ideal, but it sure wouldn't take
very long to implement. Less time than it will take to wait for a better
response from the list, probably. Unfortunately, without a convenient hql
shell into which to type ad-hoc queries to experiment with, it is difficult
to postulate a working hql query beyond what you are likely to have tried
yourself. There's no question that you can't put a subquery in the from
clause, though, so I don't see an obvious way to do it in hql.

[Landon Cheek]

You're right, writing pure SQL would be easier, however the reason I want to
use HQL is because I have environments that differ in their DB, each with
their own set of date functions, which you can see I rely on for the query.

I'd rather not have to write multiple queries that were used based on the
environment. I'd like if Hibernate just figured it out for me.

In any case, I believe what I'll do is get all the individual counts per
day, and then iterate through them to compute the average myself. This seems
to be the easiest workaround, since Hibernate seems to want to fight me all
the way.

--
View this message in context: http://grails.1312388.n4.nabble.com/HQL-Nested-Aggregate-Functions-tp4629813p4629907.html
Sent from the Grails - user mailing list archive at Nabble.com.

---------------------------------------------------------------------
To unsubscribe from this list, please visit:

http://xircles.codehaus.org/manage_email