Going back to your JPQL example, doesn't this work:
SELECT p, SIZE(p.comments) as c
FROM Post p
ORDER BY c DESC
On Wed, Jan 8, 2014 at 4:27 PM, wlad <[email protected]> wrote:
> 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.
>
