QueryDSL을 사용해 통계(Count)쿼리의 결과를 사용해서 정렬하기

도토링 프로젝트를 하다가 QueryDSL을 이용해서 count 쿼리와 이 결과를 바탕으로 정렬하는 데 삽질을 하여서 이를 포스팅하고자 합니다.

 

요구사항

 

도토링 프로젝트에서는 회원가입시 멘토와 멘티가 각각 기입했던 희망 멘토링 분야를 토대로 멘티와 멘토를 추천해준다.

(회원가입시에 멘토와 멘티 모두 희망 멘토링 분야는 복수 선택이 가능하다.)

 

이때, 멘토,멘티 정렬 방식은 멘토링 희망 분야가 많이 겹칠 수록 상위에 위치합니다.

만약 겹치는 멘토링 희망 분야의 갯수가 동일할 경우 조회가 더 많이 된 멘토 혹은 멘티가 상위에 위치합니다.

 

 

이 포스팅에서 다룰 요구사항은 멘토링 희망 분야가 많이 겹칠수록 상위에 위치한다 입니다!

 

 

---

 

위 요구사항에서 알 수 있듯이 멘토링 희망 분야가 얼마나 겹치는 지를 알아야합니다.

 

필자는 통계 쿼리를 사용해서 멘토링 희망 분야가 얼마나 겹치는 지를 나타내는 컬럼을 만들어 이 컬럼을 기준으로 정렬하려고 합니다.

 

 

먼저, 멘티를 기준으로 한번 생각해봅시다.

 

 

시나리오는 아래와 같습니다.

 

 

1. 멘티의 희망 멘토링 분야를 검색한다.

SELECT FIELD_NAME FROM DESIRED_FIELD WHERE MENTI_ID = ?

 

 

  2. 개발_언어, 공모전, 진로를 희망 멘토링 분야로 가진 멘토를 검색한다.

 

SELECT * 
FROM DESIRED_FIELD 
WHERE FIELD_NAME IN 
(SELECT FIELD_NAME FROM DESIRED_FIELD WHERE MENTI_ID = ?) 
AND MENTO_ID IS NOT NULL;

 

 

 

  3. MENTO_ID로 그룹핑하여 해당 멘토의 멘토링 유사도를 MENTO_COUNT 컬럼으로 나타냅니다.

SELECT MENTO_ID, COUNT(*) AS MENTO_COUNT
FROM 
(SELECT * FROM DESIRED_FIELD
WHERE FIELD_NAME IN (SELECT FIELD_NAME FROM DESIRED_FIELD WHERE MENTI_ID = ?) 
AND MENTO_ID IS NOT NULL)
GROUP BY MENTO_ID
ORDER BY MENTO_COUNT DESC;

 

 

 

이들을 QueryDSL로 바꾸기 전에 이 쿼리들을 한번에 처리할 지 쿼리를 나눠서 애플리케이션상에서 조립할 지를 생각해야한다.

 

 

MySQL 5.5에서는 IN subquery의 동작 속도는 느리다.

 

5.6이상에서는 대부분의 부분에서 최적화가 이루어졌지만, 최적화가 적용되어 있지 않은 부분이 존재한다.

 

 

따라서, IN subquery를 사용하지 않고 애플리케이션 상에서 조립하도록 하겠습니다!

(이 부분에 대해서는 후에 대량 데이터를 삽입하고 쿼리들을 비교해보는 포스팅을 작성하도록 하겠습니다!)

 

https://jojoldu.tistory.com/520’

 

---

 

QueryDSL

 

 

아래는 통계 쿼리를 사용해서 멘토링 희망 분야가 얼마나 겹치는 지를 나타내는 컬럼을 만들어 이 컬럼을 기준으로 정렬한 QueryDSL 코드이다.

 

@Repository
@RequiredArgsConstructor
public class QueryDesiredFieldRepository {

    private final JPAQueryFactory jpaQueryFactory;

    public List<MentoRankDTO> findMentoRank(List<String> fieldNames){

        StringPath mentoCount = Expressions.stringPath("mentoCount");
        List<MentoRankDTO> rankDTOS = jpaQueryFactory
                .select(Projections.bean(MentoRankDTO.class
                        ,desiredField.mento.mentoId.as("mentoId")
                        , desiredField.count().as("mentoCount")))
                .from(desiredField)
                .where(eqFieldNames(fieldNames), desiredField.mento.mentoId.isNotNull())
                .groupBy(desiredField.mento.mentoId)
                .orderBy(mentoCount.desc())
                .fetch();
        return rankDTOS;

    }

    private BooleanExpression eqFieldNames(List<String> fieldNames){
        if(fieldNames.size() == 0){
            return null;
        }
        return desiredField.field.fieldName.in(fieldNames);
    }

}

(fieldNames를 가져오는 쿼리는 간단하니 생략하겠습니다.)

 

 

• Projections을 사용해서 MentoRandDTO와 매핑시켰습니다.

 

엔티티를 반환하지 않고 DTO를 반환하는 이유는 아래 포스팅을 참고해주시면 감사하겠습니다.

 

https://rasony.tistory.com/155

읽기 전용 쿼리의 성능 최적화에 대한 고민

 

 

• as()는 컬럼의 이름을 지정하는 역할을 합니다

 

여기서 필자가 어려웠던 부분은 mentoCount를 기준으로 정렬시켜줘야하는데 정렬 시킬 컬럼을 만들 방법을 모른다는 것이었다.

 

이때 사용하면 좋은 것이 Expressions의 stringPath()메서드이다.

 

 

 

테스트 코드

 

@Test
void findMentoRank(){

        /**
         *  MentoAccount와 Mento, Menti는 식별관계 -> Mento id값이 2,3,4이다.
         */

        List<String> fieldNames = desiredFieldRepository.findByMentiId(1L).stream().map(df -> df.getField().getFieldName()).collect(Collectors.toList());
        List<MentoRankDTO> mentoRank = queryDesiredFieldRepository.findMentoRank(fieldNames);

        Assertions.assertThat(mentoRank.size()).isEqualTo(3);
        Assertions.assertThat(mentoRank.get(0).getMentoId()).isEqualTo(2L);
        Assertions.assertThat(mentoRank.get(0).getMentoCount()).isEqualTo(3L);

        Assertions.assertThat(mentoRank.get(1).getMentoId()).isEqualTo(3L);
        Assertions.assertThat(mentoRank.get(1).getMentoCount()).isEqualTo(1L);

        Assertions.assertThat(mentoRank.get(2).getMentoId()).isEqualTo(4L);
        Assertions.assertThat(mentoRank.get(2).getMentoCount()).isEqualTo(1L);

}

 

 

 

테스트 성공!