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.
Joined: Jul 07, 2009
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)]
Joined: Feb 12, 2009
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...
where rule.owner is null (or = null?).
if you want to use subqueries you can use not exists like in this example: