HQL Equivalent of SQL Contains

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.

Answers


try this:

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

Related article


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.


Need Your Help

Daylight saving time and time zone best practices

datetime timezone utc dst datetimeoffset

I am hoping to make this question and the answers to it the definitive guide to dealing with daylight saving time, in particular for dealing with the actual change overs.

Is rebasing DLLs (or providing an appropriate default load address) worth the trouble?

c++ c windows dll

Rebasing a DLL means to fix up the DLL such, that it's preferred load adress is the load address that the Loader is actually able to load the DLL at.