• Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

sub query in HQL

 
Cokie Burun
Greenhorn
Posts: 2
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Greenhorn
Posts: 2
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Ranch Hand
Posts: 133
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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


John
 
I agree. Here's the link: http://aspose.com/file-tools
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic