JPQL complex One to Many join query result-CodePudding

I have two simple DTOs,

class Post{
   @Id
   private int postId;
   private String text;

   @OneToMany(mappedBy = "post") 
   private List<Comment> comments = new ArrayList<Comment>();
}

class Comment{
  @Id
  private int commentId;
  private int userId;
  private into text;
  @ManyToOne
  @JoinColumn(name = "postId")
  private Post post;
}

A post can have multiple comments by different users
A post can also have multiple comments by the same user

Now, using JPQL, I want to find all the posts along with the user comments, where post has comment(s) by the given user.

So, essentially I want a list of 'Post' objects along with the qualifying comments encapsulated in each post.

Sample SQL might look like this:

select *
from post p
inner join comment c
  on p.post_id = c.post_id
where c.user_id = {given user id}

CodePudding user response：

public interface PostRepository extends CrudRepository<Integer, Post> {

    @Query("select p from Post p inner join p.comments c where c.userId =:userId")
    List<Post> findByCommentsUserId(@Param("userId") Integer userId);
}

Set<Post> posts = new HashSet<>(findByCommentsUserId("anyUserId"));

I write this code without executing it so It is pseodocode but it will be similar