I am having trouble with the following simple HQL query:
Code:
SELECT DISTINCT DARG.USERACCOUNTS
FROM DATAACCESSRESTRICTIONGROUPBO DARG
WHERE DARG.ID = :GROUPID
ORDER BY DARG.USERACCOUNTS.LOGINID ASC
The SQL created from it looks like this:
Code:
SELECT DISTINCT USERACCOUN2_.ID AS ID104_,
USERACCOUN2_.TRACE AS TRACE104_,
USERACCOUN2_.IS_ACTIVE AS IS3_104_,
USERACCOUN2_.MODIFIED AS MODIFIED104_,
USERACCOUN2_.UPDATE_USER AS UPDATE5_104_,
USERACCOUN2_.CREATED AS CREATED104_,
USERACCOUN2_.CREATE_USER AS CREATE7_104_,
USERACCOUN2_.LOGIN_ID AS LOGIN8_104_,
USERACCOUN2_.PASSWORD AS PASSWORD104_,
USERACCOUN2_.PERSON_ID AS PERSON10_104_
FROM DATA_ACCESS_RESTRICTION_GROUP DATAACCESS0_,
USER_ACCOUNT_DATA_ARG_X USERACCOUN1_,
USER_ACCOUNT USERACCOUN2_,
USER_ACCOUNT_DATA_ARG_X USERACCOUN3_,
USER_ACCOUNT USERACCOUN4_
WHERE DATAACCESS0_.ID = USERACCOUN3_.DATA_ARG_ID
AND USERACCOUN3_.USER_ACCOUNT_ID = USERACCOUN4_.ID
AND DATAACCESS0_.ID = USERACCOUN1_.DATA_ARG_ID
AND USERACCOUN1_.USER_ACCOUNT_ID = USERACCOUN2_.ID
AND DATAACCESS0_.ID = ?
ORDER BY USERACCOUN4_.LOGIN_ID ASC
My problem is that
USER_ACCOUNT_DATA_ARG_X and
USER_ACCOUNT are each joined into the SQL statement
twice thus resulting in a nasty carthesian product with bad performance. How can I avoid this from happening?
Sidenote: It is not possible for me to use explicit JOINs in HQL for programmatic reasons!
Login
FAQ
Search