title: "NOT IN `subquery`" statement with JPA Criteria API
body: <p>I'm using JPA in my Java EE application and the Criteria API to query my database (PostgreSQL).
  I implemented a tree as a Closure Table ans I'm trying to get the root nodes.
  Here's my schema (useless fields omitted):</p>
  
  <pre><code>NeedsTreev2     :
  id              | primary key
  
  NeedNode        :
  id              | primary key
  needstree_id    | foreign key references needstreev2(id)
  
  NeedLink        :
  ancestor_id     | foreign key references neednode(id)
  descendant_id   | foreign key references neednode(id)
  needstree_id    | foreign key references needstreev2(id)
  </code></pre>
  
  <p>Here's my JPA mapping</p>
  
  <pre><code>public class NeedsTreev2 {
      @Id
      private Long id;
  }
  
  public class NeedNode {
      @Id
      private Long id;
  }
  
  public class NeedLink {
      @ManyToOne
      private NeedNode ancestor;
      @ManyToOne
      private NeedNode descendant;
      @ManyToOne
      private NeedsTreev2;
  }
  </code></pre>
  
  <p>The root nodes of a tree are those which are never used as descendants, so
  here's the SQL query which returns the root nodes of a specified tree :</p>
  
  <pre><code>SELECT nNode.* FROM neednode nNode
                 INNER JOIN needstreev2 nTree
                         ON nNode.needstree_id = nTree.id
  
                 WHERE nTree.id = ?
                   AND nNode.id NOT IN
                  (SELECT nLink.descendant_id FROM needlink nLink
                                              WHERE nLink.ancestor_id != nLink.descendant_id)
                 ;
  </code></pre>
  
  <p>Then I tried to translate it with Criteria :</p>
  
  <pre><code>public List&lt;NeedNode&gt; getRootsByTree(NeedsTreev2 tree) {
          List&lt;NeedNode&gt; ret;
  
          CriteriaBuilder cb = this.getEntityManager().getCriteriaBuilder();
          CriteriaQuery&lt;NeedNode&gt; cq = cb.createQuery(NeedNode.class);
  
          Root&lt;NeedNode&gt; nNode = cq.from(NeedNode.class);
  
          /* Here we define the subquery */
          Subquery&lt;NeedNode&gt; sq = cq.subquery(NeedNode.class);
          Root&lt;NeedLink&gt; nLink = sq.from(NeedLink.class);
          sq.where(cb.notEqual(nLink.get(NeedLink_.ancestor), nLink.get(NeedLink_.descendant)));
          sq.select(nLink.get(NeedLink_.descendant));
          /* End of subquery */
  
          Predicate[] p = {
              cb.equal(nNode.get(NeedNode_.needsTree), tree),
              cb.not(cb.in(nNode).value(sq)) /* This is where the problem occurs */
          };
  
          cq.where(cb.and(p));
  
          TypedQuery&lt;NeedNode&gt; query = this.getEntityManager().createQuery(cq);
          ret = query.getResultList();
  
          return (ret);
      }
  </code></pre>
  
  <p>This code seems logical to me but it throws an exception :</p>
  
  <pre><code>org.eclipse.persistence.exceptions.QueryException
  Exception Description: Illegal use of getField() [NEEDNODE.ID] in expression.
  Query: ReadAllQuery(referenceClass=NeedNode )
  </code></pre>
  
  <p>I also tried to replace <code>cb.not(cb.in(nNode).value(sq))</code> by <code>cb.not(nNode.in(sq))</code> but it throws the same exception.</p>
  
  <p>I probably missed something but I can't find it.
  Thanks for the help.</p>