Querydsl BooleanBuilder와 BooleanExpression

[chadongmin]

날짜 필터링 기능 구현 중 WHERE절 and조건이 잘 작동하지 않는 문제점이 발생

날짜 필터링이란 RoomProduct의 startDate가 쿼리스트링으로 넘어온 startDate와 endDate의 범위이면서 stock이 1이상인 것만 조회하는 기능

현재 테이블 구조에서는 Accommodation과 Room, RoomProduct를 조인해서 조회하는 쿼리문을 작성하는 것이 옳다고 판단함.

BooleanBuilder

    @Override
    public List<Accommodation> getAccommodationByDateSearchCondition(
        SearchCondition searchCondition,
        Pageable pageable) {

        return jpaQueryFactory
            .select(accommodation)
            .from(accommodation)
            .join(accommodation.roomList, room)
            .join(room.roomProductList, roomProduct)
            .where(booleanBuilderForDate(searchCondition))
            .offset(pageable.getPageNumber())
            .limit(pageable.getPageSize())
            .fetch();

    }

    private BooleanBuilder booleanBuilderForDate(SearchCondition searchCondition) {
        BooleanBuilder booleanBuilder = new BooleanBuilder();
        //        날짜 필터링
        if (searchCondition.getStartDate() != null && searchCondition.getEndDate() != null) {
            booleanBuilder.and(
                room.roomProductList.any().startDate.between(searchCondition.getStartDate(), searchCondition.getEndDate()))
                .and(room.roomProductList.any().stock.gt(0));
        }

        return booleanBuilder;
    }

SELECT
    a1_0.accommodation_id,
    a1_0.address_code,
    a1_0.created_at,
    a1_0.deleted_at,
    a1_0.latitude,
    a1_0.like_count,
    a1_0.longitude,
    a1_0.name,
    a1_0.phone_number,
    a1_0.updated_at
FROM
    accommodation a1_0
JOIN
    room r1_0 ON a1_0.accommodation_id = r1_0.accommodation_id
JOIN
    room_product r2_0 ON r1_0.room_id = r2_0.room_id
WHERE
    EXISTS (
        SELECT 1
        FROM room_product r3_0
        WHERE r3_0.start_date BETWEEN ? AND ? AND r1_0.room_id = r3_0.room_id
    )
    AND EXISTS (
        SELECT 1
        FROM room_product r4_0
        WHERE r4_0.stock > ? AND r1_0.room_id = r4_0.room_id
    )
LIMIT
    ?;

위와 같은 쿼리문이 수행됨

요청 URI :

GET http://localhost:8080/v1/accommodations?startDate=2023-11-28&endDate=2023-11-30

11월 28일에서 11월 30일에 stock이 1 이상인 방은 7번 room만 존재한다.

7번 room은 3번 숙소의 객실이므로 3번 숙소만 조회되어야 한다. 하지만 1번숙소와 3번 숙소가 함께 조회되었다.

  "data": [
    "data": [
    {
      "id": 1,
      "name": "롯데호텔",
      "latitude": 30.0,
      "longitude": 30.0,
      "addressCode": "1111",
      "likeCount": 10,
      "phoneNumber": "02-111-111",
      "accommodationImageUrl": null,
      "page": 0,
      "size": 10,
      "price": 200000
    },
    {
      "id": 3,
      "name": "영주호텔",
      "latitude": 30.0,
      "longitude": 30.0,
      "addressCode": "1113",
      "likeCount": 2,
      "phoneNumber": "054-1231-411",
      "accommodationImageUrl": null,
      "page": 0,
      "size": 10,
      "price": 90000
    }
  ]
  ]
}

BooleanExpression

@Override
    public List<Accommodation> getAccommodationByDateSearchCondition(
        SearchCondition searchCondition,
        Pageable pageable) {

        return jpaQueryFactory
            .select(accommodation)
            .from(accommodation)
            .join(accommodation.roomList, room)
            .join(room.roomProductList, roomProduct)
            .where(betweenDate(searchCondition),
                greaterThanStock(0))
            .offset(pageable.getPageNumber())
            .limit(pageable.getPageSize())
            .fetch();

    }

 private BooleanExpression betweenDate(SearchCondition searchCondition){
        if (searchCondition.getStartDate()==null|| searchCondition.getEndDate()==null){
            return null;
        }
        return roomProduct.startDate.between(searchCondition.getStartDate(),searchCondition.getEndDate());
    }


  private BooleanExpression greaterThanStock(Integer stock){
      if (stock == null){
          return null;
      }
      return roomProduct.stock.gt(stock);
  }

위와 같이 메서드 형식으로 사용되는 BooleanExpression으로 수정한뒤, where절에 삽입함

SELECT
    a1_0.accommodation_id,
    a1_0.address_code,
    a1_0.created_at,
    a1_0.deleted_at,
    a1_0.latitude,
    a1_0.like_count,
    a1_0.longitude,
    a1_0.name,
    a1_0.phone_number,
    a1_0.updated_at
FROM
    accommodation a1_0
JOIN
    room r1_0 ON a1_0.accommodation_id = r1_0.accommodation_id
JOIN
    room_product r2_0 ON r1_0.room_id = r2_0.room_id
WHERE
    r2_0.start_date BETWEEN ? AND ? AND r2_0.stock > ?
LIMIT
    ?, ?;

BooleanBuilder를 사용했을때는 exist절이 존재했었는데, BooleanExpression을 사용하니 exist절이 사라지고 WHERE절에서 바로 AND 조건이 실행되는 모습을 확인할 수 있다.

"data": [
    {
      "id": 3,
      "name": "영주호텔",
      "latitude": 30.0,
      "longitude": 30.0,
      "addressCode": "1113",
      "likeCount": 2,
      "phoneNumber": "054-1231-411",
      "accommodationImageUrl": null,
      "page": 0,
      "size": 10,
      "price": 90000
    }
  ]

정상적으로 쿼리스트링 범위의 날짜에 존재하면서 stock이 1이상인 즉, 예약가능한 3번 숙소만 조회되는 모습을 확인할 수 있다.