JPA and Hibernate: Count number of related entries in projection

Question

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.

Answer 1

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