my dog learned polymorphism
The moose likes Object Relational Mapping and the fly likes sub query in HQL Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » Object Relational Mapping
Bookmark "sub query in HQL" Watch "sub query in HQL" New topic

sub query in HQL

Cokie Burun

Joined: Jul 07, 2009
Posts: 2

I am having trouble with a HQL query. I have a Rules table, a Owner table and a Owner_Rules that holds one to many mapping between owner and their rules (one owner has many rules). The Rules table also has rules that do not have any owners.

I have a rules class, a owner class with one to many mapping for rules (collection).

I would like to write a HQL query that gives me all the rules without any owners.
Something like this pseudo query

where not in (select from Owner as o join Rule as rule)

But it doesn't work. Any help is greatly appreciated.

Cokie Burun

Joined: Jul 07, 2009
Posts: 2
Here is the error

org.hibernate.QueryException: illegal attempt to dereference collection [Owner.AutoID.Rules] with element property reference [id] [from Rule as rl where not in (select from Onwer o)]
at org.hibernate.hql.ast.tree.DotNode$1.buildIllegalCollectionDereferenceException(
at org.hibernate.hql.ast.tree.DotNode.checkLhsIsNotCollection(
at org.hibernate.hql.ast.tree.DotNode.resolve(
at org.hibernate.hql.ast.tree.FromReferenceNode.resolve(
at org.hibernate.hql.ast.tree.FromReferenceNode.resolve(
at org.hibernate.hql.ast.tree.DotNode.resolveSelectExpression(
at org.hibernate.hql.ast.HqlSqlWalker.resolveSelectExpression(
at org.hibernate.hql.antlr.HqlSqlBaseWalker.selectExpr(
at org.hibernate.hql.antlr.HqlSqlBaseWalker.selectExprList(
at org.hibernate.hql.antlr.HqlSqlBaseWalker.selectClause(
John Bengler
Ranch Hand

Joined: Feb 12, 2009
Posts: 133
Hi Cokie,

I do not know HQL, but JPAQL and I think they are very similar (or at least they should be ).

There is a special keyword: is empty.

Assuming you make your relation bidirectional you should be able to write something like

where rule.owner is empty

But having written this I think this may only work with toMany-relations...

What about

where rule.owner is null (or = null?).

if you want to use subqueries you can use not exists like in this example:

This example is from the Hibernate documentation: Hibernate Documentation in Chapter 14.13

Have you checked out Aspose?
subject: sub query in HQL
It's not a secret anymore!