title: Criteria Query select join
body: <p>I need help to convert this JPQL query in Criteria Query:</p>
  
  <pre><code>SELECT u.documentList FROM Unit u WHERE u.id = :id
  </code></pre>
  
  <p>this is what i have tried:</p>
  
  <pre><code>CriteriaQuery&lt;Document&gt; query = builder.createQuery(Document.class);
  Root&lt;Unit&gt; root = query.from(Unit.class);
  Join&lt;Unit, Document&gt; join = root.join(Unit_.documentList);
  query.select(join);
  query.where(builder.equal(root.get(AbstractEntity_.id), entity.getId()));
  </code></pre>
  
  <p>executing this query result in a complex SQL query that returns an empty list.</p>
  
  <p>IMO this should work, maybe it's a bug?</p>
  
  <p>i'm using EclipseLink 2.5.2 as JPA provider and MySQL 5.6 as db.</p>
  
  <p>here the generated SQL:</p>
  
  <pre><code>SELECT ... 
  FROM UNIT t3 
      LEFT OUTER JOIN (DOCUMENT_RELATION t4 
      JOIN UNIT t0 ON (t0.ID = t4.CHILD_ID) 
      JOIN DELIVERABLE t1 ON (t1.ID = t0.ID) 
      JOIN DOCUMENT t2 ON (t2.ID = t0.ID)) ON (t4.PARENT_ID = t3.ID) 
  WHERE (t3.ID = 58)
  
  SELECT ... 
  FROM DOCUMENT_RELATION t6, 
      DOCUMENT t5, 
      DELIVERABLE t4, 
      UNIT t3, 
      DOCUMENT t2, 
      DELIVERABLE t1, 
      UNIT t0 
  WHERE (((t3.ID = 58) AND (((t5.ID = t3.ID) AND ((t4.ID = t3.ID) AND (t4.ID = t3.ID))) AND (t3.DTYPE = 'Document'))) AND ((((t5.ID = t3.ID) AND ((t4.ID = t3.ID) AND (t4.ID = t3.ID))) AND (t3.DTYPE = 'Document')) AND (((t6.PARENT_ID = t3.ID) AND (t0.ID = t6.CHILD_ID)) AND (((t2.ID = t0.ID) AND ((t1.ID = t0.ID) AND (t1.ID = t0.ID))) AND (t0.DTYPE = 'Document'))))) LIMIT 0, 10
  </code></pre>
  
  <p>here is the mapping:</p>
  
  <pre><code>@Entity
  @Inheritance(strategy = InheritanceType.JOINED)
  public abstract class Unit extends NamedEntity
  {
      @ManyToMany(fetch = FetchType.LAZY)
      @JoinTable(name = "DOCUMENT_RELATION", joinColumns = @JoinColumn(name = "PARENT_ID"), inverseJoinColumns = @JoinColumn(name = "CHILD_ID"))
      protected List&lt;Document&gt; documentList = new ArrayList&lt;&gt;();
  }
  </code></pre>
  
  <p>and here is the class hierarchy:</p>
  
  <pre><code>AbstractEntity (abstract @MappedSuperClass)
      NamedEntity (abstract @MappedSuperClass)
          Unit (abstract @Entity joined-inheritance)
              Deliverable (abstract @Entity)
                  Document (concrete @Entity)
  </code></pre>
  
  <hr>
  
  <p>found solution:</p>
  
  <pre><code>CriteriaQuery&lt;Document&gt; query = builder.createQuery(Document.class);
  
  Root&lt;Unit&gt; root = query.from(Unit.class);
  root.alias("root");
  
  Root&lt;Document&gt; relation = query.from(Document.class);
  relation.alias("relation");
  
  query.select(relation);
  
  query.where
  (
      builder.equal(root.get(AbstractEntity_.id), item.getId()),
      builder.isMember(relation, root.get(Unit_.actionList))
  );
  </code></pre>