title: Building JPA Criteria API query - sorting by number of elements in collection
body: <p>I am having problem with building a query with JPA Criteria API. </p>
  
  <p>Entities and significant properties:</p>
  
  <pre><code>@Entity
  public class Post {
      @Id int id;
      @OneToMany (mappedBy = "post") Set&lt;Comment&gt; comments;
      //...
  }
  
  @Entity
  public class Comment {
      @Id int id;
      @ManyToOne Post post;
      //...
  }
  </code></pre>
  
  <p>I need a query that will return all posts from db ordered by number of comments (<code>OneToMany</code> in <code>Post</code>).
  At first I thought this can be implemented with <code>JPQL</code> like: </p>
  
  <pre><code>SELECT p 
  FROM Post p 
  ORDER BY SIZE(p.comments) DESC
  </code></pre>
  
  <p>But function <code>SIZE(...)</code> can not be used to be ordered by it in <code>JPQL</code>.</p>
  
  <p>So, I found about <code>JPA Criteria API</code>, and tried following:</p>
  
  <pre><code>CriteriaBuilder cb = em.getCriteriaBuilder();
  CriteriaQuery&lt;Post&gt; cq = cb.createQuery(Post.class);
  Root&lt;Post&gt; p = cq.from(Post.class);
  cq.select(p);
  cq.orderBy(cb.desc(p.get("comments")));
  List&lt;Post&gt; resultList = em.createQuery(cq).getResultList();
  </code></pre>
  
  <p>With this query I am not getting proper results. I am aware that I am missing getting <code>size</code> of the set 'comments', but don't know how to add that part. I am not really familiar with <code>JPA Criteria API</code>. How should this query look to get all posts ordered by size of its <code>comments</code> field(set)?</p>