HQL Equivalent of SQL Contains

Question

I'm trying to write an HQL query to select objects which contain an object in a child collection.

Example:

Contest Object

ContestID  
ContestName  
RequiredCountries -> one to many collection of Country objects

Country Object

CountryCode  
CountryName

The sql equivalent of what i want:

SELECT * FROM CONTEST C  
WHERE C.CONTESTID IN(SELECT CONTESTID FROM CONTEST_COUNTRY CC INNER JOIN COUNTRY CTRY ON   
CC.COUNTRYCODE = CTRY.COUNTRYCODE WHERE COUNTRYCODE='USA')

OR

SELECT * FROM CONTEST C  
WHERE EXISTS(SELECT CONTESTID FROM CONTEST_COUNTRY CC INNER JOIN COUNTRY CTRY ON 
CC.COUNTRYCODE = CTRY.COUNTRYCODE WHERE COUNTRYCODE='USA' AND CC.CONTESTID=C.CONTESTID)

I have this hql, which works, but seems like not a good solution-

from Contest C  
where (from Country where CountryCode = :CountryCode) =  some elements(C.RequiredCountries)

I also consider joining with Country, but since I don't have an object class to represent the relationship, I wasn't sure how to join in HQL.

Anyone have any ideas or suggestions? This should be easy.

score 38 · Accepted Answer · answered Feb 26 '09 at 21:29

38

try this:

from Contest Ct, Country Cr
where Cr.CountryCode = :CountryCode 
    and Cr.Country in elements(Ct.RequiredCountries)

Maksym Gontar

22,765
10
78
114

Exactly what i needed, creates an IN statement in SQL – Brian Feb 26 '09 at 21:52

score 8 · Answer 2 · answered Aug 01 '12 at 15:05

The previous one will work (at least it works for me --- I'm using hibernate), but the 'proper way' is the 'member of' operator... like this:

select ...
from Contest Ct, Country Cr
where Cr.CountryCode = :CountryCode
  and Cr.Country member of Ct.RequiredCountries

(see http://docs.oracle.com/javaee/6/tutorial/doc/bnbuf.html#bnbvk)

The elements() is HQL extension, I think. I think it's better to use the standard (JPQL) whenever possible.

HQL Equivalent of SQL Contains

2 Answers2