Building JPA Criteria API query - sorting by number of elements in collection

Question

I am having problem with building a query with JPA Criteria API.

Entities and significant properties:

@Entity
public class Post {
    @Id int id;
    @OneToMany (mappedBy = "post") Set<Comment> comments;
    //...
}

@Entity
public class Comment {
    @Id int id;
    @ManyToOne Post post;
    //...
}

I need a query that will return all posts from db ordered by number of comments (OneToMany in Post). At first I thought this can be implemented with JPQL like:

SELECT p 
FROM Post p 
ORDER BY SIZE(p.comments) DESC

But function SIZE(...) can not be used to be ordered by it in JPQL.

So, I found about JPA Criteria API, and tried following:

CriteriaBuilder cb = em.getCriteriaBuilder();
CriteriaQuery<Post> cq = cb.createQuery(Post.class);
Root<Post> p = cq.from(Post.class);
cq.select(p);
cq.orderBy(cb.desc(p.get("comments")));
List<Post> resultList = em.createQuery(cq).getResultList();

With this query I am not getting proper results. I am aware that I am missing getting size of the set 'comments', but don't know how to add that part. I am not really familiar with JPA Criteria API. How should this query look to get all posts ordered by size of its comments field(set)?

Answer 1

CriteriaBuilder.size(Expression) returns an Expression<Integer> that you may use in the ORDER BY clause. This line of code:

p.get("comments")

..returns a Path<X> which extends Expression<X> so it is safe to use the returned value as an argument to Collection.size().

However, the previously quoted line of code is using a particular overloaded version of Path.get() which will make it impossible for the compiler to infer type parameter X. Instead, the type argument will be assumed to be Object. But Collection.size() has declared his Expression-parameter to be a "parameterized type" with an "upper bound" of Collection (this is not reflected accurately in the first reference to CriteriaBuilder.size() in my answer, StackOverflow insist on erasing the type from the method signature. Please see the JavaDocs instead!). So we must provide the type argument explicitly.

Try this:

cq.orderBy(cb.desc(cb.size(p.<Collection>get("comments"))));