sub query in HQL

[Cokie Burun]

Hello,

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 Rule.id not in (select rule.id from Owner as o join Rule as rule)

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

[Cokie Burun]

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 rl.id not in (select o.Rules.id from Onwer o)]
at org.hibernate.hql.ast.tree.DotNode$1.buildIllegalCollectionDereferenceException(DotNode.java:69)
at org.hibernate.hql.ast.tree.DotNode.checkLhsIsNotCollection(DotNode.java:536)
at org.hibernate.hql.ast.tree.DotNode.resolve(DotNode.java:244)
at org.hibernate.hql.ast.tree.FromReferenceNode.resolve(FromReferenceNode.java:117)
at org.hibernate.hql.ast.tree.FromReferenceNode.resolve(FromReferenceNode.java:113)
at org.hibernate.hql.ast.tree.DotNode.resolveSelectExpression(DotNode.java:649)
at org.hibernate.hql.ast.HqlSqlWalker.resolveSelectExpression(HqlSqlWalker.java:761)
at org.hibernate.hql.antlr.HqlSqlBaseWalker.selectExpr(HqlSqlBaseWalker.java:1885)
at org.hibernate.hql.antlr.HqlSqlBaseWalker.selectExprList(HqlSqlBaseWalker.java:1825)
at org.hibernate.hql.antlr.HqlSqlBaseWalker.selectClause(HqlSqlBaseWalker.java:1394)

[John Bengler]

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:

    
from Cat as cat
where not exists (
    from Cat as mate where mate.mate = cat
)

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


John