title: JPA 2 + Criteria API - Defining a subquery
body: <p>I try to convert a sql query to Criteria API without success so far. I can create two separate queries which return the values I need, but I don't know how to combine them in a single query.</p>
  
  <p>Here is the sql statement which works:</p>
  
  <pre><code>select company.*, ticketcount.counter from company
      join 
  (select company, COUNT(*) as counter from ticket where state&lt;16 group by company) ticketcount
  on company.compid = ticketcount.company;
  </code></pre>
  
  <p>This Criteria query returns the inner query results:</p>
  
  <pre><code>CriteriaBuilder cb = em.getCriteriaBuilder();
  CriteriaQuery&lt;intCompany&gt; qTicket = cb.createQuery(intCompany.class);
  Root&lt;Ticket&gt; from = qTicket.from(Ticket.class);
  Path groupBy = from.get("company");        
  Predicate state = cb.notEqual(from.&lt;State&gt;get("state"), getStateById(16));        
  qTicket.select(cb.construct(
      intCompany.class, cb.count(from),from.&lt;Company&gt;get("company")))
          .where(state).groupBy(groupBy);                
  em.createQuery(qTicket).getResultList();
  </code></pre>
  
  <p>In the application I defined a small wrapper/helper class:</p>
  
  <pre><code>public  class intCompany{
      public Company comp;
      public Long opentickets;
      public intCompany(Long opentickets,Company comp){
          this.comp = comp;
          this.opentickets = opentickets;
      }
      public intCompany(){
  
      }
  }
  </code></pre>
  
  <p>So does anyone has an idea how to get this working?</p>
  
  <p><strong>Update</strong></p>
  
  <p>Thank you. I changed my criteria query as you suggested. I just had to add a loop at the end to get the information I wanted.</p>
  
  <pre><code>List&lt;intCompany&gt; result = em.createQuery(cq).getResultList();
  List&lt;Company&gt; cresult = new ArrayList();
      for(intCompany ic: result){
          ic.comp.setOpentickets(ic.opentickets.intValue());
          cresult.add(ic.comp);
      }
  return cresult;
  </code></pre>
  
  <p>Maybe it is just not possible to convert the original sql to Criteria API.</p>
  
  <p><strong>Another update</strong></p>
  
  <p>I figured out I had to change the original sql expression to </p>
  
  <pre><code>select company.*, ticketcount.counter from company
      left join 
  (select company, COUNT(*) as counter from ticket where state&lt;16 group by company) ticketcount
  on company.compid = ticketcount.company;
  </code></pre>
  
  <p>Otherwise I do not get companies with no entries in the ticket table.</p>
  
  <p>So are there any other suggestions?</p>