Hibernate version: 2.1.4
Hi everyone,
I am having an issue with getting compound paths to expand correctly with AND / OR criteria in the WHERE clause of an HQL statement.
In our app, a Profile has an email and Contact. The Contact has an Address. I need to be able to search on email OR (city AND state).
Here is my HQL:
Code:
FROM UserProfile as profile WHERE profile.email = ? OR (profile.profileContact.address.city = ? AND profile.profileContact.address.state = ?)
I have the show_sql turned on and see it translates to the following:
Code:
select userprofil0_.USER_PROFILE_ID as USER_PRO1_,
userprofil0_.COMPANY_USER_ID as COMPANY_2_,
userprofil0_.EMAIL as EMAIL,
userprofil0_.AUTO_LOGIN_ID as AUTO_LOG4_,
userprofil0_.COCODE as COCODE,
userprofil0_.CHALLENGE_QUESTION as CHALLENG6_,
userprofil0_.CHALLENGE_ANSWER as CHALLENG7_,
userprofil0_.ACTIVE as ACTIVE,
userprofil0_.PROFILE_TYPE as PROFILE_9_,
userprofil0_.AGREE_TO_TERMS as AGREE_T10_,
userprofil0_.PROFILE_VIEWED as PROFILE11_,
userprofil0_.ADD_PROFILE_ID as ADD_PRO12_,
userprofil0_.USER_ID as USER_ID,
userprofil0_.ADD_DBUSER as ADD_DBUSER,
userprofil0_.ADD_DATE as ADD_DATE,
userprofil0_.MOD_PROFILE_ID as MOD_PRO16_,
userprofil0_.MOD_DBUSER as MOD_DBUSER,
userprofil0_.MOD_DATE as MOD_DATE,
userprofil0_.PROFILE_CONTACT_ID as PROFILE19_,
userprofil0_.REMIT_CONTACT_ID as REMIT_C20_
from CPS_USER_PROFILE userprofil0_,
CPS_CONTACT contact1_,
CPS_ADDRESS address2_
where (userprofil0_.EMAIL = ?)
OR ((address2_.CITY = ? and
userprofil0_.PROFILE_CONTACT_ID = contact1_.CONTACT_ID and
contact1_.ADDRESS_ID = address2_.ADDRESS_ID) AND
(address2_.STATE = ? and
userprofil0_.PROFILE_CONTACT_ID = contact1_.CONTACT_ID and
contact1_.ADDRESS_ID = address2_.ADDRESS_ID))
I run this SQL in a PL/SQL developer client, and it never comes back. The inner join criteria really should be outside the search criteria. As a test, I edited the hibernate-generated SQL WHERE clause manually so it reads:
Code:
where (userprofil0_.EMAIL = 'myemail@test.com'
OR (address2_.CITY = 'Tampa' and address2_.STATE = 'FL'))
AND
userprofil0_.PROFILE_CONTACT_ID = contact1_.CONTACT_ID and
contact1_.ADDRESS_ID = address2_.ADDRESS_ID
and ran in PL/SQL developer. This worked great.
Does anyone know how I can restructure the HQL statement so that it will expand the compound paths in this manner?
I could drop back to SQL (which is the backup plan), but I would like to try to get it working as an HQL statement.
Thank you very much in advance.
Sincerely,
P.J.
Login
FAQ
Search
