I'm trying to get the following query to work in Hibernate:
SELECT m FROM MyEntity m WHERE m.owner = :user
AND m.field1 IN (:field1Vals)
AND m.field2 IN (:field2Vals)
AND m.obj1 IS NOT NULL
AND (
m.obj1.count > 0
OR
(m.obj2 IS NOT NULL
AND m.obj2.count > 0)
)
ORDER BY m.createDate DESC
The problem is, it seems to always evaluate the part that goes:
AND (
m.obj1.count > 0
OR
(m.obj2 IS NOT NULL
AND m.obj2.count > 0)
)
...as:
AND (m.obj2 IS NOT NULL
AND m.obj2.count > 0)
In other words, it is only returning objects that satisfy the condition that follows the OR, and ignoring any results that satisfy the first condition. If I remove the condition following the OR, the query correctly brings back objects that satisfy the m.obj1.count > 0 condition.
I assume I've made some basic error in how I have structured my query, but I'm not sure what it might be. Any ideas?
Update
I've found a variant that works, it uses a self-join and adds some superfluous parenthesis:
SELECT DISTINCT m FROM MyEntity m, MyEntity m2 WHERE m.owner = :user
AND m.field1 IN (:field1Vals)
AND m.field2 in (:field2Vals)
AND m.obj1 IS NOT NULL
AND (
(m.obj1.count > 0)
OR
(m2.obj2 IS NOT NULL
AND (m2.obj2.count > 0))
)
ORDER BY m.createDate DESC"
This same query without the self-join does not work. Same goes for the seemingly superfluous parenthesis. With them, it returns an incorrect result.
So now my question is, why is it necessary to structure the query this way, both with respect to the self-join and the "superfluous" parenthesis?
It may be worth noting that obj1 and obj2 are different instances of the same foreign entity. So the table that I am querying is referencing two different rows in a single foreign table. I suspect that this is the reason why a self-join was necessary (and it is why I decided to try a self-join), but I'm not sure exactly what the reason behind it is. Any explanation would be welcome.
To get around this issue I ultimately had to revise my query as follows:
SELECT DISTINCT m FROM MyEntity m, MyEntity m2 WHERE m.owner = :user
AND m.field1 IN (:field1Vals)
AND m.field2 in (:field2Vals)
AND m.obj1 IS NOT NULL
AND (
(m.obj1.count > 0)
OR
(m2.obj2 IS NOT NULL
AND (m2.obj2.count > 0))
)
ORDER BY m.createDate DESC
I thank Randy for his very good suggestion, but in my specific case it did not work, and I had to rewrite my original query using a self-join in order to get the correct result back.