[Querydsl] 서브쿼리

 

- com.querydsl.jpa.JPAExpressions 사용

 

/**
 * 서브 쿼리
 * 나이가 가장 많은 회원 조회
 */
@Test
public void subQuery() {
    QMember memberSub = new QMember("memberSub");

    List<Member> result = queryFactory
            .selectFrom(member)
            .where(member.age.eq(
                    JPAExpressions
                            .select(memberSub.age.max())
                            .from(memberSub)
            ))
            .fetch();

    assertThat(result).extracting("age").containsExactly(40);

}

/**
 * 나이가 평균 나이 이상인 회원
 */
@Test
public void subQueryGoe(){
    QMember memberSub = new QMember("memberSub");

    List<Member> result = queryFactory
            .selectFrom(member)
            .where(member.age.goe(
                    JPAExpressions
                            .select(memberSub.age.avg())
                            .from(memberSub)
            ))
            .fetch();

    assertThat(result).extracting("age").containsExactly(30, 40);

}

/**
 * 서브쿼리 여러 건 처리, in 사용
 */
@Test
public void subQueryIn(){
    QMember memberSub = new QMember("memberSub");

    List<Member> result = queryFactory
            .selectFrom(member)
            .where(member.age.in(
                    JPAExpressions
                            .select(memberSub.age)
                            .from(memberSub)
                            .where(memberSub.age.gt(10))
            ))
            .fetch();

    assertThat(result).extracting("age").containsExactly(20, 30, 40);

}