I'm trying to write an HQL query to grab a list of users that belong to a particular organisation, or any franchisee from a list of franchisees, however hibernate isn't able to parse it. I can't figure out why. Here is the HQL:
from User u where
(u.parentOrganisation = :topLevelOrganisation or u.parentOrganisation in :franchisees)
and u.parentOrganisation.deleted = false
and u.active = true
This is the error that hibernate spits out:
unexpected AST node: {vector} [from com.myapp.User u where (u.parentOrganisation = :topLevelOrganisation or u.parentOrganisation in :franchisees0_, :franchisees
1_, :franchisees2_) and u.parentOrganisation.deleted = false and u.active = true]. Stacktrace follows:
Message: unexpected AST node: {vector} [from com.myapp.User u where (u.parentOrganisation = :topLevelOrganisation or u.parentOrganisation in :franchisees0_, :fr
anchisees1_, :franchisees2_) and u.parentOrganisation.deleted = false and u.active = true]
If I take out the or u.parentOrganisation in :franchisees
bit, so my query looks like this:
from User u where
(u.parentOrganisation = :topLevelOrganisation)
and u.parentOrganisation.deleted = false
and u.active = true
Then it works fine. What's wrong with my syntax? Why is hibernate complaining about that extra clause?
Oh, it turns out that I needed to enclose :franchisees
in parentheses:
from User u where
(u.parentOrganisation = :topLevelOrganisation or u.parentOrganisation in (:franchisees))
and u.parentOrganisation.deleted = false
and u.active = true
The reason why does this happen is because when data in array is put in the list without parentheses, query syntax for searching database from the list will be wrong.
EXAMPLE:
List<Integer> userIdList = [0, 1, 2, 3]
Query without parentheses: from User u where u.id in :list
will look like this when data is inserted from User u where u.id in 0, 1, 2, 3
- WRONG SYNTAX.
Query with parentheses: from User u where u.id in (:list)
will look like this when data is inserted from User u where u.id in (0, 1, 2, 3)
- CORRECT SYNTAX.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With