HQL Equivalent of SQL Contains
I'm trying to write an HQL query to select objects which contain an object in a child collection.
ContestID ContestName RequiredCountries -> one to many collection of Country objects
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')
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.
from Contest Ct, Country Cr where Cr.CountryCode = :CountryCode and Cr.Country in elements(Ct.RequiredCountries)
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
The elements() is HQL extension, I think. I think it's better to use the standard (JPQL) whenever possible.