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

[wlad]

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` relation 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)?



--
View this message in context: 
http://openjpa.208410.n2.nabble.com/JPA-Criteria-API-query-sorting-by-number-of-elements-in-collection-tp7585829.html
Sent from the OpenJPA Users mailing list archive at Nabble.com.