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)?
See Question&Answers more detail:
os 与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…