CODEWITHSUNDEEP
javasqlspringhibernateorm
Rick moriarty
Rick moriarty

Reputation: 27

spring+Hibernate one to many count rows

I have a One To Many relationship between "Post" and "Comment" entities in hibernate. One Post Can have many comments (Post) 1 - N (Comment), therefore comment holds foreign key to post (post_id). The thing is that When i'm fetching post list for my controller, with each post i also want to include number of COMMENTS assigned to this post. So if there is some post list, i want to attach to the data number of comments below that post.

I've created PostWithCommentsCountDTO with additional field that can hold number of comments, i've done converters etc.

BUT Where and how should i actually fetch the number of comments, how can i actually do that ?

Whole architecture looks like this Controllers - PostWithCommentsCountDTO - PostService - PostRepository - Post Controllers - CommentDTO - CommentService - CommentRepository - Comment

Upvotes: 0

Views: 1122

Answers (1)

Taras Shpulyar
Taras Shpulyar

Reputation: 134

Please read point 15.6 here You can create constructor with all fields for PostWithCommentsCountDTO. After that You can use it inside HQL query as it is described in the link I've added. In you query you can join Post and Comments by 'post_id', group them by 'post_id' and use count(post_id) in select statement. So Your query will look like :

select new PostWithCommentsCountDTO (p.id, /and all other fields.../ , count(post_id)) from Post as p left join Comments as c on p.id=c.post_id where /some where statement if needed .../ group by c.post_id

This means that You can collect Your DTO already in repository level:

public List<PostWithCommentsCountDTO> getWithCommentsCount(...){
     return (List<PostWithCommentsCountDTO>) getSession().createQuery(...).setParameter(...).list();
}

Upvotes: 1

Related Questions