Hi, I'm just learning the intricacies of HQL, and I've stumbled upon something I need to do that I can't figure out how to implement in HQL. I need to order a list of object by a sub - sub - property - meaning that it is a property two layers down in a set of associations.

The SQL looks like this:
select series.id as seriesId,
(select min(sub_net.network_id) as netId
from network as sub_net
join ownership_snapshot_network as sub_sn on sub_sn.network_id = sub_net.id
join ownership_snapshot as sub_snap on sub_snap.id = sub_sn.ownership_snapshot_networks_id
join ownership_snapshot_series as sub_series on sub_snap.series_id = sub_series.id
where sub_series.id = seriesId
) as lowest_license
from ownership_snapshot_series as series
join ownership_snapshot as snap ON snap.series_id = series.id
join ownership_snapshot_network as sn on sn.ownership_snapshot_networks_id = snap.id
join network as net on net.id = sn.network_id
group by series.id
order by lowest_license

I read that HQL would let me do subqueries in the where and in the select clause, bu it doesn't seem to be working. I've tried several different things. I'm using grails here, so the table names with underscores match up to the same name with CamelCase

First I tried this:
select oss from OwnershipSnapshotSeries as oss order by min (oss.snapshots.networks.networkId)

Which gave me an illegal attempt to dereference a collection. I looked that up, and it said I needed some joins in there, so then I tried this:

select oss from OwnershipSnapshotSeries as oss \
left join fetch oss.snapshots snap \
left join fetch snap.networks as network \
order by network.networkId

But it said (of course, now that I think about it) that the order by item had to be in the select statement. So then I started looking how to just reproduce the SQL in HQL:
select distinct oss, min(select sub_net.networkId from Network sub_net \
where sub_net.id = network.id \
) as lowest_license\
from OwnershipSnapshotSeries as oss \
join oss.snapshots snap \
join snap.networks network \
order by lowest_license \
group by oss

But I get an error on the select statement - unexpected token: select. Which makes me think maybe subselects in the select clause aren't supported?

So what I need to know is, how can I do what I'm trying to do in HQL? Or even with a Criteria?

Hibernate version: 3 (what ships with grails)

Mapping documents: generated by grails, basically class names match up to table names as the camel case is converted to underscores.

Code between sessionFactory.openSession() and session.close():
(grails version)
def query1 = "select distinct oss, min(select sub_net.networkId from Network sub_net \
where sub_net.id = network.id \
) as lowest_license\
from OwnershipSnapshotSeries as oss \
join oss.snapshots snap \
join snap.networks network \
order by lowest_license \
group by oss "
def results = OwnershipSnapshotSeries.executeQuery(query1)

Full stack trace of any exception that occurs:
could not execute query; nested exception is org.hibernate.exception.SQLGrammarException: could not execute query

org.springframework.dao.InvalidDataAccessResourceUsageException: could not execute query; nested exception is org.hibernate.exception.SQLGrammarException: could not execute query at org.springframework.orm.hibernate3.SessionFactoryUtils.convertHibernateAccessException(SessionFactoryUtils.java:615) at org.springframework.orm.hibernate3.HibernateAccessor.convertHibernateAccessException(HibernateAccessor.java:412) at org.springframework.orm.hibernate3.HibernateTemplate.doExecute(HibernateTemplate.java:424) at org.springframework.orm.hibernate3.HibernateTemplate.executeFind(HibernateTemplate.java:343) at org.codehaus.groovy.grails.orm.hibernate.metaclass.ExecuteQueryPersistentMethod.doInvokeInternal(ExecuteQueryPersistentMethod.java:61) at

(the rest of the stack is very useless and mostly grails related)

Name and version of the database you are using:
5.0.45-community-nt MySQL Community Edition (GPL)

The generated SQL (show_sql=true):
doesn't get there

Debug level Hibernate log excerpt:
2009-01-27 15:25:51,934: [8954] hql.PARSER line 1:26: unexpected token: select
2009-01-27 15:25:51,934: [8954] hql.PARSER line 1:26: unexpected token: select
2009-01-27 15:25:51,934: [8954] hql.PARSER line 1:51: expecting CLOSE, found 'from'
2009-01-27 15:25:51,934: [8954] hql.PARSER line 1:51: expecting CLOSE, found 'from'
2009-01-27 15:25:51,934: [8954] ast.HqlParser processEqualityExpression() : No expression to process!
2009-01-27 15:25:51,934: [8954] ast.HqlParser processEqualityExpression() : No expression to process!

Thank you in advance for any help you can provide.

Actually a select is not allowed within min(). Try to switch it (just like in SQL):

thank you for the response! But I can't get it working... :-(

When I try this:
select distinct oss, (select min(sub_net.networkId) from Network sub_net \
where sub_net.id = network.id \
) lowest_license\
from OwnershipSnapshotSeries oss \
join oss.snapshots snap \
join snap.networks network \
order by lowest_license

It says it doesn't recognize lowest_license:
2009-01-28 11:36:22,736: [9530] hql.PARSER line 1:134: unexpected token: lowest_license
2009-01-28 11:36:22,736: [9530] hql.PARSER line 1:134: unexpected token: lowest_license
2009-01-28 11:36:22,736: [9530] ast.HqlParser processEqualityExpression() : No expression to process!

How do I make it understand? :-(

Try "(...) as lowest_license"

that gives me this error:
2009-01-28 14:03:37,690: [9358] util.JDBCExceptionReporter SQL Error: -28, SQLState: S0022
2009-01-28 14:03:37,690: [9358] util.JDBCExceptionReporter SQL Error: -28, SQLState: S0022
2009-01-28 14:03:37,690: [9358] util.JDBCExceptionReporter Column not found: LOWEST_LICENSE in statement [select distinct ownerships0_.id as col_0_0_, (select min(network4_.network_id) from network network4_ where network4_.id=network3_.id) as col_1_0_, ownerships0_.id as id12_, ownerships0_.version as version12_, ownerships0_.country_id as country3_12_, ownerships0_.created_by_id as created4_12_, ownerships0_.date_created as date5_12_, ownerships0_.is_complete as is6_12_, ownerships0_.last_updated as last7_12_, ownerships0_.updated_by_id as updated8_12_ from ownership_snapshot_series ownerships0_ inner join ownership_snapshot snapshots1_ on ownerships0_.id=snapshots1_.series_id inner join ownership_snapshot_network networks2_ on snapshots1_.id=networks2_.ownership_snapshot_networks_id inner join network network3_ on networks2_.network_id=network3_.id order by lowest_license]
2009-01-28 14:03:37,690: [9358] util.JDBCExceptionReporter Column not found: LOWEST_LICENSE in statement [select distinct ownerships0_.id as col_0_0_, (select min(network4_.network_id) from network network4_ where network4_.id=network3_.id) as col_1_0_, ownerships0_.id as id12_, ownerships0_.version as version12_, ownerships0_.country_id as country3_12_, ownerships0_.created_by_id as created4_12_, ownerships0_.date_created as date5_12_, ownerships0_.is_complete as is6_12_, ownerships0_.last_updated as last7_12_, ownerships0_.updated_by_id as updated8_12_ from ownership_snapshot_series ownerships0_ inner join ownership_snapshot snapshots1_ on ownerships0_.id=snapshots1_.series_id inner join ownership_snapshot_network networks2_ on snapshots1_.id=networks2_.ownership_snapshot_networks_id inner join network network3_ on networks2_.network_id=network3_.id order by lowest_license]


--
could not execute query; nested exception is org.hibernate.exception.SQLGrammarException: could not execute query

org.springframework.dao.InvalidDataAccessResourceUsageException: could not execute query; nested exception is org.hibernate.exception.SQLGrammarException: could not execute query at org.springframework.orm.hibernate3.SessionFactoryUtils.convertHibernateAccessException(SessionFactoryUtils.java:615) at org.springframework.orm.hibernate3.HibernateAccessor.convertHibernateAccessException(HibernateAccessor.java:412) at org.springframework.orm.hibernate3.HibernateTemplate.doExecute(HibernateTemplate.java:424) at

Your database probably does not understand aliases at order by, try this:

Maybe your group by won't work. Than you have to replace oss with all properties of oss, e.g.: "group by oss.id, oss.name..."

Hi, thanks. I get this error:

2009-01-28 16:42:55,603: [10170] hql.PARSER line 1:293: unexpected token: select
2009-01-28 16:42:55,603: [10170] hql.PARSER line 1:293: unexpected token: select

It was my understanding you couldn't put a select in an order by clause... am I wrong on that? I thought it was just select and where. But I've been reading so much it could be all confused.

I've run the query as raw sql with an alias in the order by, and not had any problems. So I'm not sure that's the problem...

Hmm, there are two thinks that make me wonder:
1.
Actually your subselect does not get the right alias. Could you post the code, where you execute the query.

2. The "normal" order of "group by" and "order by" : "group by x order by y". Try to switch the order.

Hi, thanks yet again.

I'm using grails, so I'm afraid a lot of it is under the covers. But here it is, anyways:

def query1 = """select distinct oss, (select min(sub_net.networkId) from Network sub_net \
where sub_net.id = network.id \
) as lowest_license \
from OwnershipSnapshotSeries oss \
join oss.snapshots snap \
join snap.networks network \
group by oss.id
order by select min(sub_net.networkId) from Network sub_net \
where sub_net.id = network.id \
) """
def results = OwnershipSnapshotSeries.executeQuery(query1)

I still get the error on the select, even with group by first.

Why do you need a subselect at all? "join snap.networks network" does relate to the Network-table, right?

So you could just do the following:


Ratings are welcome ;-)

Well, I thought I read you couldn't put min in an order by clause, but I may be confusing that with subselects.

At any rate, I get an error when I do that:
2009-01-29 17:23:28,211: [51204] util.JDBCExceptionReporter SQL Error: -67, SQLState: 37000
2009-01-29 17:23:28,211: [51204] util.JDBCExceptionReporter SQL Error: -67, SQLState: 37000
2009-01-29 17:23:28,211: [51204] util.JDBCExceptionReporter Not in aggregate function or group by clause: org.hsqldb.Expression@163d118 in statement [select distinct ownerships0_.id as id27_, ownerships0_.version as version27_, ownerships0_.country_id as country3_27_, ownerships0_.created_by_id as created4_27_, ownerships0_.date_created as date5_27_, ownerships0_.is_complete as is6_27_, ownerships0_.last_updated as last7_27_, ownerships0_.updated_by_id as updated8_27_ from ownership_snapshot_series ownerships0_ inner join ownership_snapshot snapshots1_ on ownerships0_.id=snapshots1_.series_id inner join ownership_snapshot_network networks2_ on snapshots1_.id=networks2_.ownership_snapshot_networks_id inner join network network3_ on networks2_.network_id=network3_.id group by ownerships0_.id order by min(network3_.network_id)]
2009-01-29 17:23:28,211: [51204] util.JDBCExceptionReporter Not in aggregate function or group by clause: org.hsqldb.Expression@163d118 in statement [select distinct ownerships0_.id as id27_, ownerships0_.version as version27_, ownerships0_.country_id as country3_27_, ownerships0_.created_by_id as created4_27_, ownerships0_.date_created as date5_27_, ownerships0_.is_complete as is6_27_, ownerships0_.last_updated as last7_27_, ownerships0_.updated_by_id as updated8_27_ from ownership_snapshot_series ownerships0_ inner join ownership_snapshot snapshots1_ on ownerships0_.id=snapshots1_.series_id inner join ownership_snapshot_network networks2_ on snapshots1_.id=networks2_.ownership_snapshot_networks_id inner join network network3_ on networks2_.network_id=network3_.id group by ownerships0_.id order by min(network3_.network_id)]



Funny thing is, when I put the generated SQL into my database's query editor, it works....

select
distinct ownerships0_.id as id27_,
ownerships0_.version as version27_,
ownerships0_.country_id as country3_27_,
ownerships0_.created_by_id as created4_27_,
ownerships0_.date_created as date5_27_,
ownerships0_.is_complete as is6_27_,
ownerships0_.last_updated as last7_27_,
ownerships0_.updated_by_id as updated8_27_
from
ownership_snapshot_series ownerships0_
inner join
ownership_snapshot snapshots1_
on ownerships0_.id=snapshots1_.series_id
inner join
ownership_snapshot_network networks2_
on snapshots1_.id=networks2_.ownership_snapshot_networks_id
inner join
network network3_
on networks2_.network_id=network3_.id
group by
ownerships0_.id
order by
min(network3_.network_id)