[Spring/fairer] 반복 기능 api 개발 - 조회

반복 기능 구현 방법을 설계하고, 나는 조회 api를 담당했다. 반복기능 구현 전 집안일 api 에서도 조회를 담당했는데, 굉장히 비효율적으로 구현했던 전적이 있어서 이번에는 개선해 보고자 도전했는데 …

 

fromDate ~ toDate에 속한 집안일을 조회하려면

• 반복 종류에 따라서
  • ONCE (반복x)
    1. scheduled_date가 조회 범위 안에 속하는지 확인
  • DAILY (매일 반복)
    1. scheduled_date ~ end_date가 조회 범위와 겹치는 구간이 있는지 확인
    2. repeat_exception 테이블에 조회할 날짜가 등록되어 있는지 확인
  • WEEKLY (요일 반복)
    1. scheduled_date ~ end_date가 조회 범위와 겹치는 구간이 있는지 확인
    2. (repeat_pattern에 저장된 요일 == 조회할 날짜의 요일) 확인
    3. repeat_exception 테이블에 조회할 날짜가 등록되어 있는지 확인
  • MONTHLY (날짜 반복)
    1. scheduled_date ~ end_date가 조회 범위와 겹치는 구간이 있는지 확인
    2. (repeat_pattern에 저장된 날짜 == 조회할 날짜) 확인
    3. repeat_exception 테이블에 조회할 날짜가 등록되어 있는지 확인

 

v1. 처음에 작성한 코드

• HouseworkController

    @Tag(name = "houseWorks")
    @ApiOperation(value = "특정 날짜별 집안일 조회 - 반복 기능 구현 후", notes = "특정 날짜별 집안일 조회")
    @GetMapping("/list/v3")
    public ResponseEntity<Map<String, HouseWorkDateResponseDto>> getHouseWorkListByDateV3(@RequestParam("fromDate") String fromDate,
                                                                                          @RequestParam("toDate") String toDate,
                                                                                          @ApiIgnore @RequestMemberId Long memberId) {
        final LocalDate from = DateTimeUtils.stringToLocalDate(fromDate);
        final LocalDate to = DateTimeUtils.stringToLocalDate(toDate);

        Member member = memberService.find(memberId);

        // 날짜별 집안일dto 생성
        List<HouseWork> houseWorkList = houseWorkService.getHouseWorkByDateAndTeamRepeat(member.getTeam(), from, to);
        Map<LocalDate, List<HouseWorkResponseDto>> houseWorkListGroupByScheduledDate = getHouseWorkListGroupByScheduledDateRrule(houseWorkList, from, to);

        // 완료 여부 세팅
        for(List<HouseWorkResponseDto> dtos : houseWorkListGroupByScheduledDate.values()){
            for(HouseWorkResponseDto houseWorkResponseDto : dtos){
                houseWorkResponseDto.setSuccess(houseWorkService.getHouseWorkCompleted(houseWorkResponseDto.getHouseWorkId(), houseWorkResponseDto.getScheduledDate()));
            }
        }

        return ResponseEntity.ok(makeHouseWorkListResponse(memberId, houseWorkListGroupByScheduledDate));
    }

1. getHouseWorkListGroupByScheduledDateRrule(houseWorkList, from, to) 를 통해 fromDate~toDate에 속한 집안일을 반복 주기별로 조건을 달아 List로 모두 가져온다.

• HouseWorkCustomRepositoryImpl

@Override
public List<HouseWork> getCycleHouseWorkByTeam(LocalDate fromDate, LocalDate toDate, Team team) {

    return new ArrayList<>(jpaQueryFactory.selectFrom(houseWork)
            .where(((houseWork.rrule.contains("ONCE")
                    .and(houseWork.scheduledDate.between(fromDate, toDate)))
                    .and(houseWork.team.eq(team)))

                    .or((houseWork.rrule.contains("EVERY")
                            .and(houseWork.scheduledDate.loe(toDate))
                            .and(houseWork.repeatEndDate.goe(fromDate)))
                            .and(houseWork.team.eq(team)))

                    .or((houseWork.rrule.contains("WEEKLY")
                            .and(houseWork.scheduledDate.loe(toDate))
                            .and(houseWork.repeatEndDate.goe(fromDate)))
                            .and(houseWork.team.eq(team)))

                    .or((houseWork.rrule.contains("MONTHLY")
                            .and(houseWork.scheduledDate.loe(toDate))
                            .and(houseWork.repeatEndDate.goe(fromDate)))
                            .and(houseWork.team.eq(team)))
            )
            .fetch());
}

 

 

2. getHouseWorkListGroupByScheduledDateRrule 함수를 통해 받아온 List를 날짜별로 매핑하여 반환한다.(Map<LocalDate, List<HouseWorkResponseDto>>)

 private Map<LocalDate, List<HouseWorkResponseDto>> getHouseWorkListGroupByScheduledDateRrule(List<HouseWork> houseWorkList, LocalDate fromDate, LocalDate toDate) {

        Map<LocalDate, List<HouseWorkResponseDto>> result = new HashMap<>();
        Stream.iterate(fromDate, date -> date.plusDays(1))
                .limit(ChronoUnit.DAYS.between(fromDate, toDate) + 1).forEach(date -> {
                    List<HouseWorkResponseDto> houseWorkResponseDtos = new ArrayList<>();
                    for(HouseWork houseWork : houseWorkList) {
                        if (periodCheck(date, houseWork)) {
                            List<String> repeatRule = parsing(houseWork.getRrule());

                            if (repeatRule.get(0).equals("ONCE")) {
                                if (DateTimeUtils.stringToLocalDate(repeatRule.get(1)).equals(date)) {
                                    houseWorkResponseDtos.add(getRepeatHouseWorkResponseDto(houseWork, date));
                                }
                            } else if (repeatRule.get(0).equals("EVERY") && !exceptionCheck(houseWork, date)) {
                                houseWorkResponseDtos.add(getRepeatHouseWorkResponseDto(houseWork, date));
                            } else if (repeatRule.get(0).equals("WEEKLY") && !exceptionCheck(houseWork, date)) {
                                if (repeatRule.get(1).contains(date.getDayOfWeek().toString())){
                                    houseWorkResponseDtos.add(getRepeatHouseWorkResponseDto(houseWork, date));
                                }
                            } else if (repeatRule.get(0).equals("MONTHLY") && !exceptionCheck(houseWork, date)) {
                                if (Integer.parseInt(repeatRule.get(1)) == (date.getDayOfMonth())) {
                                    houseWorkResponseDtos.add(getRepeatHouseWorkResponseDto(houseWork, date));
                                }
                            }
                        }
                    }
                    if(!houseWorkResponseDtos.isEmpty()) {
                        result.put(date, houseWorkResponseDtos);
                    }
                });
        return result;
    }

처음에 이런식으로 구현했던 이유는, housework 테이블의 full-scan 횟수를 최소화하기 위함이었다. 날짜별로 쿼리를 실행하기 보다는 한 번의 쿼리로 모든 집안일을 가져와서 날짜별로 분류하는 것이 효율적이라고 생각했다.

 

하지만.. 이미 쿼리에서 반복 종류, 날짜까지 비교해서 뽑아온 데이터들을 for문을 통해 다시 분류하는 것은, 굉장히 비효율적이었다.

예를 들어 2023-01-10 ~ 2023-01-17까지의 집안일을 조회할 때,

1. 쿼리를 통해 2023-01-10 ~ 2023-01-16(7일) 에 해당되는 집안일 100개를 가져옴
2. for문을 통해 100개의 집안일을 7번씩 비교함
3. 아래의 로직을 700번 수행해야 함

 if (repeatRule.get(0).equals("ONCE")) {
        if (DateTimeUtils.stringToLocalDate(repeatRule.get(1)).equals(date)) {
            houseWorkResponseDtos.add(getRepeatHouseWorkResponseDto(houseWork, date));
        }
    } else if (repeatRule.get(0).equals("EVERY") && !exceptionCheck(houseWork, date)) {
        houseWorkResponseDtos.add(getRepeatHouseWorkResponseDto(houseWork, date));
    } else if (repeatRule.get(0).equals("WEEKLY") && !exceptionCheck(houseWork, date)) {
        if (repeatRule.get(1).contains(date.getDayOfWeek().toString())){
            houseWorkResponseDtos.add(getRepeatHouseWorkResponseDto(houseWork, date));
        }
    } else if (repeatRule.get(0).equals("MONTHLY") && !exceptionCheck(houseWork, date)) {
        if (Integer.parseInt(repeatRule.get(1)) == (date.getDayOfMonth())) {
            houseWorkResponseDtos.add(getRepeatHouseWorkResponseDto(houseWork, date));
        }
    }

게다가 exeptionCheck(houseWork, date)도 여기에서 호출되고 있어 700번의 select문이 추가로 수행된다.🫣

 

v2. 피드백 받고 수정한 코드 - 하루에 쿼리 하나씩 수행되도록

• HouseworkController

    @Tag(name = "houseWorks")
    @ApiOperation(value = "특정 날짜별 집안일 조회 - 반복 기능 구현 후", notes = "특정 날짜별 집안일 조회")
    @GetMapping("/list/query")
    public ResponseEntity<Map<String, HouseWorkDateResponseDto>> getHouseWorkListByDateQuery(@RequestParam("fromDate") String fromDate,
                                                                                             @RequestParam("toDate") String toDate,
                                                                                             @ApiIgnore @RequestMemberId Long memberId) {
        final LocalDate from = DateTimeUtils.stringToLocalDate(fromDate);
        final LocalDate to = DateTimeUtils.stringToLocalDate(toDate);

        Member member = memberService.find(memberId);
        Map<LocalDate, List<HouseWorkResponseDto>> results = new HashMap<>();

        Stream.iterate(from, date -> date.plusDays(1))
                .limit(ChronoUnit.DAYS.between(from, to) + 1).forEach(date -> {
                    List<HouseWorkResponseDto> houseWorkResponseDtoList = houseWorkService.getHouseWorkByDateRepeatTeamQuery(member.getTeam(), date).stream().map(arr -> {
                        List<MemberDto> memberDtoList = memberService.getMemberListByHouseWorkId(arr.getHouseWork().getHouseWorkId())
                                .stream().map(MemberDto::from).collect(Collectors.toList());
                        return HouseWorkResponseDto.from(arr.getHouseWork(), memberDtoList, date, arr.getHouseWorkCompleteId());
                    }).collect(Collectors.toList());

                    results.put(date, houseWorkResponseDtoList);
                });

        return ResponseEntity.ok(makeHouseWorkListResponse(memberId, results));
    }

 

 

날짜별로 getHouseWorkByDateRepeatTeamQuery(member.getTeam(), date) 를 호출해 조회할 날짜에 해당하는 집안일들을 쿼리한다.

    @Override
    public List<Object[]> getCycleHouseWorkQuery(LocalDate date, Long memberId) {

        List<Tuple> results =  jpaQueryFactory.select(houseWork,
                        JPAExpressions.selectFrom(houseworkComplete)
                                .where(houseworkComplete.houseWork.houseWorkId.eq(houseWork.houseWorkId)
                                        .and(houseworkComplete.scheduledDate.eq(date))).isNotNull())
                .from(houseWork)
                .innerJoin(houseWork.assignments, assignment)
                .innerJoin(assignment.member, member)
                .where((houseWork.repeatCycle.eq(RepeatCycle.ONCE)
                        .and(houseWork.scheduledDate.eq(date)))

                        .or(houseWork.repeatCycle.eq(RepeatCycle.DAILY)
                                .and(member.memberId.eq(memberId))
                                .and(houseWork.repeatEndDate.isNotNull())
                                .and(houseWork.scheduledDate.loe(date))
                                .and(houseWork.repeatEndDate.goe(date))
                                .and(getException(houseWork, date)))

                        .or(houseWork.repeatCycle.eq(RepeatCycle.DAILY)
                                .and(member.memberId.eq(memberId))
                                .and(houseWork.repeatEndDate.isNull())
                                .and(houseWork.scheduledDate.loe(date))
                                .and(getException(houseWork, date)))

                        .or(houseWork.repeatCycle.eq(RepeatCycle.WEEKLY)
                                .and(member.memberId.eq(memberId))
                                .and(houseWork.repeatEndDate.isNotNull())
                                .and(houseWork.scheduledDate.loe(date))
                                .and(houseWork.repeatEndDate.goe(date))
                                .and(houseWork.repeatPattern.contains(date.getDayOfWeek().toString()))
                                .and(getException(houseWork, date)))

                        .or(houseWork.repeatCycle.eq(RepeatCycle.WEEKLY)
                                .and(member.memberId.eq(memberId))
                                .and(houseWork.repeatEndDate.isNull())
                                .and(houseWork.scheduledDate.loe(date))
                                .and(houseWork.repeatPattern.contains(date.getDayOfWeek().toString()))
                                .and(getException(houseWork, date)))

                        .or(houseWork.repeatCycle.eq(RepeatCycle.MONTHLY)
                                .and(member.memberId.eq(memberId))
                                .and(houseWork.repeatEndDate.isNotNull())
                                .and(houseWork.scheduledDate.loe(date))
                                .and(houseWork.repeatEndDate.goe(date))
                                .and(houseWork.repeatPattern.castToNum(Integer.class).eq(date.getDayOfMonth()))
                                .and(getException(houseWork, date)))

                        .or(houseWork.repeatCycle.eq(RepeatCycle.MONTHLY)
                                .and(member.memberId.eq(memberId))
                                .and(houseWork.repeatEndDate.isNull())
                                .and(houseWork.scheduledDate.loe(date))
                                .and(houseWork.repeatPattern.castToNum(Integer.class).eq(date.getDayOfMonth()))
                                .and(getException(houseWork, date)))
                ).fetch();

        return results.stream().map(Tuple::toArray).collect(Collectors.toList());
    }

이렇게 날마다 쿼리를 통해 집안일을 가져오면, 추가적 로직을 통해 검증할 필요가 없어지고 날짜별로 한 번 더 분류할 필요도 없어진다. 하지만 쿼리가 너어무 복잡하므로 리팩토링을 해보자.

 

v3. 쿼리 리팩토링

    @Override
    public List<HouseWorkQueryResponseDto> getCycleHouseWorkByTeamQuery(LocalDate date, Team team) {
        return jpaQueryFactory
                .selectDistinct(Projections.fields(HouseWorkQueryResponseDto.class,
                        houseWork,
                        houseworkComplete.houseWorkCompleteId))
                .from(houseWork)
                .innerJoin(houseWork.assignments, assignment)
                .innerJoin(assignment.member, member)
                .leftJoin(houseworkComplete).on(houseworkComplete.houseWork.eq(houseWork)
                        .and(houseworkComplete.scheduledDate.eq(date)))
                .where(houseWork.team.eq(team).and(houseWork.scheduledDate.loe(date).and(houseWork.repeatEndDate.isNull().or(houseWork.repeatEndDate.goe(date))))
                        .and((houseWork.repeatCycle.eq(RepeatCycle.ONCE).and(houseWork.repeatPattern.eq(date.format(DateTimeFormatter.ofPattern("yyyy-MM-dd")))))
                                .or(houseWork.repeatCycle.eq(RepeatCycle.DAILY).and(getException(houseWork, date)))
                                .or(houseWork.repeatCycle.eq(RepeatCycle.WEEKLY).and(houseWork.repeatPattern.contains(date.getDayOfWeek().toString())).and(getException(houseWork, date)))
                                .or(houseWork.repeatCycle.eq(RepeatCycle.MONTHLY).and(houseWork.repeatPattern.castToNum(Integer.class).eq(date.getDayOfMonth())).and(getException(houseWork, date))))
                ).fetch();
    }

• 중복되는 조건 합치기
• Projections를 통해 쿼리 결과값 받기
  : Object[]를 통해 결과값을 받고 별도 로직을 작성해 파싱해 주었는데, Projections을 통해 엔티티와 다른 반환 타입을 정의하여 받아오도록 수정하였다.

---

fairer의 반복 기능을 설계하고 개발한 시기는 디프만 활동이 종료된 이후였지만, 반복 기능을 하면서 디프만에 들어오길 정말 잘했다 여러번 생각했다. 반복 기능을 개발하며 앞으로 코드를 작성할 때 어떤 부분들을 중요시해야할지 방향이 잡혔다. 그리고 파트원 모두가 하나의 기능을 같이 설계하며 피드백을 주고받은 건 처음이었는데, 본인의 의견에 확신을 갖고 설득하는 모습이 다들 멋있다 느꼈다.