Hey guys,

I'm trying to rewrite the following query so that it doesn't use a subquery, for efficiency reasons:

SELECT a FROM A a WHERE (SELECT COUNT(*) FROM B b WHERE b.condition = :condition AND b.a = a) = 0

i.e., I am looking for all A records that are NOT tied to a B record with specific properties. A also has a field A.bs, which is a collection of B's. The SQL query I'm looking for is:

SELECT a FROM A a LEFT JOIN B b ON b.condition = 'condition' AND b.a = a.id WHERE b IS NULL

The tricky part here is the additional condition on the collection. I don't seem to find a way to do this without a subquery. Anyone got an idea here? I'm at the end of my wits.

I also tried the following two queries, but those don't work:
SELECT a FROM A a LEFT JOIN a.bs b WITH b.condition = :condition WHERE b IS NULL
SELECT a FROM A a LEFT JOIN B b ON b.a = a AND b.condition = :condition WHERE b IS NULL

Not sure if this is exactly what you're trying to accomplish, but it should point you in the right direction.

This HQL should create something that is similar to the SQL that you say you are looking for... So either the SQL you posted is not generating the correct result either or there is some other details that you have missed (I assume the posted code is a simplification of the real case). I would turn on SQL logging to see what Hibernate really generates and then execute that manually against the database. Then, try to fix the SQL so that the result is correct and as a last step "backport" the fix to the HQL.

Another possibility is to use a simpler subselect that only has to be evaluated once:

Hey, thanks for the second HQL, that is just perfect!

But the first one does not work, it triggers the following exception:

Caused by: org.hibernate.hql.ast.InvalidWithClauseException: with clause can only reference columns in the driving table [SELECT s FROM com.sileni.cq3.server.hibernate.CastleSpot s LEFT JOIN s.accounts a WITH a.kingdom = :kingdom WHERE s.kingdomSpot = :kingdomSpot AND a IS NULL]

Note that the query is identical to the example I gave here, except for the names. Any idea what's wrong?

Hmm... I have seen that error before but it was a long time ago now... If I remember correctly it was a due to that the extra condition was on a many-to-one association. If your a.kingdom is a many-to-one you could try: WITH a.kingdom.id = ...