[JPA] 대댓글 구현 (페이징 + join 카운팅 / JPQL / Pageable, LIMIT OFFSET 실행 비교)

강의에 따른 댓글을 불러오는데 댓글의 대댓글이 몇개인지 함께 들고오고 싶었다.

 

먼저 SQL의 로직은

1. join: Comment(as c)와 Comment(as child)를 c.id = chile.parentId AND c.lecture = lecture의 조건으로 join 한다.

2. group by c.id를 해준다.

3. c.parentComment가 null 인 c와 count(child)를 select

4. pageable를 이용하거나 offset이용

그냥 pageable를 사용하는 것과 직접 limit offset을 사용하는것에 실행되는 쿼리를 비교하고 싶어서 작성한 글이다.

Entity

@Entity
@Getter
@Table(name = "comments")
@NoArgsConstructor(access = AccessLevel.PROTECTED)
@SQLDelete(sql = "UPDATE comments SET deleted_date_time = NOW(), content = '삭제된 댓글입니다.' WHERE comment_id = ?")
public class Comment extends BaseEntity {

  @Id
  @GeneratedValue(strategy = IDENTITY)
  @Column(name = "comment_id")
  private Long id;

  @ManyToOne(fetch = FetchType.LAZY)
  @JoinColumn(name = "parent_comment_id")
  private Comment parentComment;

  @ManyToOne(fetch = FetchType.LAZY)
  @JoinColumn(name = "author_id")
  private User author;

  @ManyToOne(fetch = FetchType.LAZY)
  @JoinColumn(name = "lecture_id", nullable = false)
  private Lecture lecture;

  @Column(nullable = false)
  private String content;

  private LocalDateTime deletedDateTime;
  //
 }

 

JPQL + Pageable

repository

@EntityGraph(attributePaths = "author")
  @Query("SELECT new com.lectureservice.domain.comment.repository.dto"+
      ".CommentWithChildCountDto(c, COUNT(child)) " +
      "FROM Comment c LEFT JOIN Comment child ON c.id = child.parentComment.id AND c.lecture = :lecture " +
      "WHERE c.parentComment IS NULL " +
      "GROUP BY c.id ")
  Page<CommentWithChildCountDto> findParentCommentsWithChildCount(Lecture lecture, Pageable pageable);

사용

private final static Pageable COMMENT_PAGEABLE
      = PageRequest.of(0,5, Sort.by(Order.desc("createdDateTime")));

commentRepository.findParentCommentsWithChildCount(lecture, COMMENT_PAGEABLE);

실행 결과 쿼리문

Hibernate: 
    select
        c1_0.comment_id,
        a1_0.user_id,
        a1_0.address,
        a1_0.created_date_time,
        a1_0.email,
        a1_0.gender,
        a1_0.modified_date_time,
        a1_0.password,
        a1_0.phone_number,
        a1_0.role,
        c1_0.content,
        c1_0.created_date_time,
        c1_0.deleted_date_time,
        c1_0.lecture_id,
        c1_0.modified_date_time,
        c1_0.parent_comment_id,
        count(c2_0.comment_id) 
    from
        comments c1_0 
    left join
        comments c2_0 
            on c1_0.comment_id=c2_0.parent_comment_id 
            and c1_0.lecture_id=? 
    left join
        users a1_0 
            on a1_0.user_id=c1_0.author_id 
    where
        c1_0.parent_comment_id is null 
    group by
        c1_0.comment_id 
    order by
        c1_0.created_date_time desc 
    offset
        ? rows 
    fetch
        first ? rows only
Hibernate: 
    select
        count(c1_0.comment_id) 
    from
        comments c1_0 
    left join
        comments c2_0 
            on c1_0.comment_id=c2_0.parent_comment_id 
            and c1_0.lecture_id=? 
    where
        c1_0.parent_comment_id is null 
    group by
        c1_0.comment_id

JPQL + LIMIT + OFFSET

@EntityGraph(attributePaths = "author")
  @Query("SELECT new com.lectureservice.domain.comment.repository.dto"+
      ".CommentWithChildCountDto(c, COUNT(child)) " +
      "FROM Comment c LEFT JOIN Comment child ON c.id = child.parentComment.id AND c.lecture = :lecture " +
      "WHERE c.parentComment IS NULL " +
      "GROUP BY c.id "+
      "ORDER BY c.createdDateTime desc "+
      "LIMIT :size OFFSET :offset")
  List<CommentWithChildCountDto> findParentCommentsWithChildCount(Lecture lecture, int size, int offset);

사용

private final static int PAGE = 0;
private final static int SIZE = 5;
private final static int OFFSET = PAGE * SIZE;
commentRepository.findParentCommentsWithChildCount(lecture, SIZE, OFFSET);

실행 결과 쿼리문

Hibernate: 
    select
        c1_0.comment_id,
        a1_0.user_id,
        a1_0.address,
        a1_0.created_date_time,
        a1_0.email,
        a1_0.gender,
        a1_0.modified_date_time,
        a1_0.password,
        a1_0.phone_number,
        a1_0.role,
        c1_0.content,
        c1_0.created_date_time,
        c1_0.deleted_date_time,
        c1_0.lecture_id,
        c1_0.modified_date_time,
        c1_0.parent_comment_id,
        count(c2_0.comment_id) 
    from
        comments c1_0 
    left join
        comments c2_0 
            on c1_0.comment_id=c2_0.parent_comment_id 
            and c1_0.lecture_id=? 
    left join
        users a1_0 
            on a1_0.user_id=c1_0.author_id 
    where
        c1_0.parent_comment_id is null 
    group by
        c1_0.comment_id 
    order by
        c1_0.created_date_time desc 
    offset
        ? rows 
    fetch
        first ? rows only

 

테스트

INSERT INTO lectures (title, price, introduction, category, teacher_id, created_date_time)
VALUES
    ('Spring Boot Fundamentals', 100, 'Learn the basics of Spring Boot.', 'SPRING', 1, NOW()),
    ('Advanced React Techniques', 150, 'Master advanced techniques in React.', 'REACT', 2, NOW()),
    ('Node.js for Beginners', 120, 'Introduction to Node.js development.', 'NODE', 3, NOW()),
    ('Full-Stack Development with Spring and React', 200, 'Become a full-stack developer using Spring and React.', 'SPRING', 1, NOW()),
    ('Ai and Machine Learning Basics', 180, 'Get started with AI and machine learning.', 'REACT', 2, NOW());

-- Lecture 1 (Spring Boot Fundamentals) 댓글
INSERT INTO comments (parent_comment_id, author_id, lecture_id, content, created_date_time)
VALUES
    (NULL, 1, 1, '정말 좋은 강의입니다!', '2023-01-01 10:00:00'),
    (NULL, 2, 1, '아주 유익합니다.', '2023-01-01 11:00:00'),
    (NULL, 3, 1, '많이 배웠어요.', '2023-01-01 12:00:00'),
    (NULL, 4, 1, '강력 추천합니다!', '2023-01-01 13:00:00'),
    (NULL, 5, 1, '훌륭한 자료입니다.', '2023-01-01 14:00:00'),
    (NULL, 1, 1, 'Spring Boot에 대한 좋은 소개입니다.', '2023-01-01 15:00:00'),
    (NULL, 2, 1, '초보자에게 딱 맞는 강의입니다.', '2023-01-01 16:00:00'),
    (NULL, 3, 1, '잘 정리된 강의입니다.', '2023-01-01 17:00:00'),
    (NULL, 4, 1, '명확하고 간결합니다.', '2023-01-01 18:00:00'),
    (NULL, 5, 1, '강의를 즐겼습니다.', '2023-01-01 19:00:00');


-- Lecture 1 (Spring Boot Fundamentals) 대댓글
INSERT INTO comments (parent_comment_id, author_id, lecture_id, content, created_date_time)
VALUES
    (1, 2, 1, '저도 동의합니다!', NOW()),
    (1, 3, 1, '저도요.', NOW()),
    (1, 4, 1, '절대적으로 동의합니다.', NOW()),
    (4, 5, 1, '저도 추천합니다.', NOW()),
    (4, 1, 1, '정말 훌륭한 자료입니다.', NOW()),
    (6, 2, 1, '소개 부분이 좋았어요.', NOW()),
    (7, 3, 1, '초보자에게 정말 좋습니다.', NOW()),
    (8, 4, 1, '아주 잘 설명되었어요.', NOW()),
    (8, 5, 1, '간결하고 명확합니다.', NOW()),
    (8, 1, 1, '저도 즐겼습니다!', NOW());

1번 댓글에 3개

4번 댓글에 2개

6번 댓글에 1개

7번 댓글에 1개

8번 댓글에 3개

실행결과

List<CommentWithChildCountDto> findParentCommentsWithChildCount(Lecture lecture, int size, int offset) 결과

{
  "id": 1,
  "title": "Spring Boot Fundamentals",
  "price": 100,
  "introduction": "Learn the basics of Spring Boot.",
  "category": "SPRING",
  "teacher": {
    "teacherId": 1,
    "name": "John Doe",
    "careerYears": 5,
    "company": "TEAM_SPARTA",
    "introduction": "Experienced software engineer specializing in backend development."
  },
  "likeCount": null,
  "comments": [
    {
      "commentId": 10,
      "authorId": 5,
      "authorEmail": "e@m",
      "content": "강의를 즐겼습니다.",
      "replyCount": 0,
      "createdDateTime": "2023-01-01T19:00:00"
    },
    {
      "commentId": 9,
      "authorId": 4,
      "authorEmail": "d@m",
      "content": "명확하고 간결합니다.",
      "replyCount": 0,
      "createdDateTime": "2023-01-01T18:00:00"
    },
    {
      "commentId": 8,
      "authorId": 3,
      "authorEmail": "c@m",
      "content": "잘 정리된 강의입니다.",
      "replyCount": 3,
      "createdDateTime": "2023-01-01T17:00:00"
    },
    {
      "commentId": 7,
      "authorId": 2,
      "authorEmail": "a@m",
      "content": "초보자에게 딱 맞는 강의입니다.",
      "replyCount": 1,
      "createdDateTime": "2023-01-01T16:00:00"
    },
    {
      "commentId": 6,
      "authorId": 1,
      "authorEmail": "b@m",
      "content": "Spring Boot에 대한 좋은 소개입니다.",
      "replyCount": 1,
      "createdDateTime": "2023-01-01T15:00:00"
    }
  ]
}

결과는 pageable도 offset을 사용하는 것으로 보였다!
다만 전체 페이지의 수를 알고자하는 쿼리문이 하나 더 나갔다.

 

나중에 참고하기: no offset

https://jojoldu.tistory.com/528