I have a simple one-to-many relation. A Post
can have many Comments
. My goal is to fetch a post by id and the number (count) of associated comments.
I'm using Kotlin so all code is just a simplified demonstration
@Entity(name = "Post")
@Table(name = "post")
public class Post {
@Id
@GeneratedValue
private Long id;
private String title;
private String text;
@OneToMany(
cascade = CascadeType.ALL,
orphanRemoval = true
)
private List<Comment> comments = new ArrayList<>();
//Constructors, getters and setters removed for brevity
}
@Entity(name = "Comment")
@Table(name = "comment")
public class Comment {
@Id
@GeneratedValue
private Long id;
private String review;
//Constructors, getters and setters removed for brevity
}
Now I need to fetch the Post with the number of comments. I thought of using a dto projection.
public class PostWithCount {
private Long id;
private String title;
private String text;
private Long numberOfComments;
}
I constructed the following jpql Query, but don't know how to count the number of comments.
@Query("select new my.package.PostWithCount(post.id, post.title, post.text, ???) from Post post left join post.comments comment where post.id = :id")
What would be the best way to count the comments without fetching them all and count in java code? I'm also open to other solutions than DTO Projection.
Use aggregation...
select new my.package.PostWithCount(post.id, post.title, post.text, count(1))
from Post post
left join post.comments comment
where post.id = :id
group by post.id, post.title, post.text