[Spring Boot] 41. 스프링부트 블로그 v2 (JPA) (11) 게시글 좋아요 2 - 한방쿼리로 해결하기

1. 한 방 쿼리

1.

2. Query

1. 내가 짠 쿼리

select b.id,b.title, b.content, b.is_public, b.created_at, u.username, 
case when b.user_id = 1 then true 
else false
end as is_owner,
case when lt.user_id = 1 then true
else false
end as is_love,
(select count(*) from love_tb lo where lo.board_id = b.id) love_count
from board_tb b
inner join user_tb u on b.user_id = u.id
left join
(select 
lo.id,
lo.user_id,
lo.board_id board_id
from love_tb lo 
where lo.user_id =1) lt on b.id = lt.board_id
where b.id = 4;

1. 선생님이 짠 쿼리
1. 1단계 - 4번 게시물이 내용이 풍부하니까 4번 기준으로

select * 
from board_tb bt where bt.id = 4;



2. 2단계 - love join (inner join을 하면 좋아요 안한건 안나오는 문제가 생긴다.)

select * 
from board_tb bt left outer join love_tb lot on bt.id=lot.board_id
where bt.id = 4;

-컬럼 정리
select bt.id, bt.title, bt.content, bt.created_at, lot.id, lot.user_id
from board_tb bt left outer join love_tb lot on bt.id=lot.board_id
where bt.id = 4;



3. is_love (1)

select bt.id, bt.title, bt.content, bt.created_at, lot.id, lot.user_id, true is_love
from board_tb bt left outer join (select * from love_tb where user_id = 3) lot on bt.id=lot.board_id
where bt.id = 4 ;



4. is_love(2) - 완성

select bt.id, bt.title, bt.content, bt.created_at, case when lot.id is null then false else true end is_love
from board_tb bt left outer join (select * from love_tb where user_id = 3) lot on bt.id=lot.board_id
where bt.id = 4 ;



5. is_love(3) - 스칼라 서브쿼리로 하는 방법 (exist 등 여러가지 방법 있음)

select bt.id, bt.title, bt.content, bt.created_at, 
ifnull((select true from love_tb where user_id = 3 and board_id = 4), false) is_love
from board_tb bt
where bt.id = 4;

6. love_count

select bt.id, bt.title, bt.content, bt.created_at, 
ifnull((select true from love_tb where user_id = 3 and board_id = 4), false) is_love,
(select count(id) from love_tb where board_id = 4) love_count
from board_tb bt
where bt.id = 4;

2. BoardResponse.DetailDTO

1. @AllArgsConstructor를 추가해준다.

package shop.mtcoding.blog.board;

import lombok.AllArgsConstructor;
import lombok.Data;

import java.sql.Timestamp;

public class BoardResponse {

    @AllArgsConstructor
    @Data
    public static class DetailDTO {
        private Integer id;
        private String title;
        private String content;
        private Boolean isPublic;
        private Boolean isOwner; // 값이 안 들어갈 경우: Boolean - null / boolean - 0
        private String username; // User 객체를 다 들고 갈 필요X
        private Timestamp createdAt;
        private Long loveCount; // 그룹함수로 리턴되는 숫자는 Long 타입
        private Boolean isLove;
    }
}

3. BoardRepository

1. DetailDTO의 필드 순서와 select로 조회되는 컬럼의 순서가 동일해야 한다.

  	// 한방쿼리 (h2 query, om -> dto)
    // BoardResponse$DetailDTO : static일때만 $를 사용한다.
    public BoardResponse.DetailDTO findDetail(Integer boardId, Integer userId) {
        String sql = """
                SELECT new shop.mtcoding.blog.board.BoardResponse$DetailDTO(
                    b.id,
                    b.title,
                    b.content,
                    b.isPublic,
                    CASE WHEN b.user.id = :userId THEN true ELSE false END,
                    b.user.username,
                    b.createdAt,
                    (SELECT COUNT(l.id) FROM Love l WHERE l.board.id = :boardId),
                    (SELECT CASE WHEN COUNT(l2) > 0 THEN true ELSE false END
                     FROM Love l2
                     WHERE l2.board.id = :boardId AND l2.user.id = :userId)
                )
                FROM Board b
                WHERE b.id = :boardId
                """;
        Query query = em.createQuery(sql);
        query.setParameter("boardId", boardId);
        query.setParameter("userId", userId);
        return (BoardResponse.DetailDTO) query.getSingleResult();
    }

4. BoardRepositoryTest

@Test
    public void findByIdJoinUser_test() {
        // given
        Integer boardId = 4;
        Integer userId = 1;
        // when
        BoardResponse.DetailDTO detailDTO = boardRepository.findDetail(boardId, userId);
        // eye
        System.out.println(detailDTO);
    }

5. BoardService

public BoardResponse.DetailDTO 글상세보기(Integer id, Integer userId) {
        BoardResponse.DetailDTO detailDTO = boardRepository.findDetail(id, userId);
        return detailDTO;
    }