title: hibernate criteria putting a contains / does not contain restriction on a set of elements
body: <p>Hi I'm trying to migrate my old sql based query framework to be Hibernate Criteria based, currently I'm using Hibernate version 3.2. and mysql v 5.0.54</p>
  
  <p>My question is I want to write a criteria that returns all the Beans that <strong>do not</strong> have a value in a contained set of elements.</p>
  
  <p>A summary of the hibernate config file for the bean is:</p>
  
  <pre><code>    &lt;hibernate-mapping package="com.mydomain.beans"&gt;
  
         &lt;typedef class="com.mydomain.dao.util.HibernateAgentOptionType" name="agent-option"/&gt;
  
         &lt;class name="Agent" table="agent"&gt;
             ...
             &lt;set name="agentOptions" table="agent_options" fetch="join" lazy="true"&gt;
                &lt;key column="agt_id"/&gt;
                &lt;element column="identifier" type="agent-option"/&gt;
             &lt;/set&gt;
         &lt;/class&gt;
     &lt;/hibernate-mapping&gt;
  </code></pre>
  
  <p>The class file is something like: </p>
  
  <pre><code>public class Agent {
     ...
     public Set&lt;Option&gt; getAgentOptions() {
        return agentOptions;
     }
     public void setAgentOptions(Set&lt;Option&gt; _agentOptions) {
        this.agentOptions = _agentOptions;
     }
  
     public enum Option {
        WEB_SITE_SYNDICATE ("web-site-syndicate"),
        RECEIVE_PREMIUM_ENQUIRIES ("receive-premium-enquiries"), 
        DO_NOT_SYNDICATE_ADS_TO_THIRD_PARTIES ("do-not-syndicate-ads-to-third-parties")
     }
  }
  </code></pre>
  
  <p>I would like to retrieve all the agents who <strong>do not have</strong> the Option "DO_NOT_SYNDICATE_ADS_TO_THIRD_PARTIES" in their agentOptions</p>
  
  <p>the sql that I was using for this previously was something like: </p>
  
  <pre><code>SELECT agt.id 
  FROM agent agt
  WHERE agt.id NOT IN (
     SELECT sub_agt.id FROM agent sub_agt 
     JOIN agent_options AS agt_options ON agt_options.agt_id = sub_agt.id
     WHERE agt_options.identifier = 'do_not_syndicate_ads_to_third_parties'
  )
  </code></pre>
  
  <p>I've tried this: </p>
  
  <pre><code>   ...
     Criteria crit = getHibernateSession().createCriteria(Agent.class);
     crit.addRestriction(Restriction.not(Restriction.in("agentOptions", noSyndicatedAds)));
     ...
  </code></pre>
  
  <p>Which spits out bad sql</p>
  
  <p>I've also tried this using aliases:</p>
  
  <pre><code>   ...
     crit.createAlias("agentOptions", "agentOption");
     crit.add(Restrictions.not(Restrictions.in("agentOption", options)));
     ...
  </code></pre>
  
  <p>Which throws an exception: </p>
  
  <p>org.hibernate.MappingException: collection was not an association: Agent.agentOptions</p>
  
  <p>Finally I decided to try a brute sql Criterion: </p>
  
  <pre><code>...
     crit.add(Restrictions.sqlRestriction(" {alias}.id not in " +
                 "(select agt_sub.id " +
                 "from agent agt_sub " +
                 "join agent_options as agent_options_sub on agent_options_sub.agt_id = agt_sub.id " +
                 "where agent_options_sub.identifier in (?)) ",
                 Agent.Option.DO_NOT_SYNDICATE_ADS_TO_THIRD_PARTIES.getIdentifier(),
                 new org.hibernate.type.StringType()
           ));
     ...
  </code></pre>
  
  <p>Which worked but seems a little bit ugly.</p>
  
  <p>Does anyone know if there is a way using the std Criteria API without having to resort to sql.</p>
  
  <p>Any help or suggestions would be very welcome.  Even if they require upgrading to a more recent version of Hibernate.</p>
  
  <p>Cheers
  Simon</p>