java - Hibernate "not in" problem -

i trying create "not in" query using hibernate criteria. trying persons don't know language, have entity looks like:

public class person {     ...     private list<language> languages;     ... }  public class language {      public long id;     public string label; } 

and criteria code looks like

criteria cr = createcriteriaforperson() // created criteria   cr.createcriteria("languages").add(restrictions.not(restrictions.in("id", values))); 

this returns persons including ones have language.

if try search persons have know specific language, equivalent query returns correct results

criteria cr = createcriteriaforperson() // created criteria   cr.createcriteria("languages").add(restrictions.in("id", values)); 

what problem?

thanks makis

i'm pretty sure cannot express query in criteria api without using sqlrestriction.

naive approach produces query this:

select p person p join p.languages l l.id not in :values 

it's not want since other languages still selected.

to express desired query need complex join

select p person p left join p.languages l l.id in :values l null 

or subquery

select p person p not exists      (select l language l l in elements(p.languages) , l.id in :values) 

since criteria api doesn't support joins conditions, way express query use sqlrestriction subqery (exact form of subqery depends on database schema):

cr.add(restrictions.sqlrestriction(     "not exists (select ... l.person_id = {alias}.id , ...)"));