QueryDsl SubQuery

Pre-Information

• version : querydsl:4.1.4

Domain-model

Count subquery in projection

QueryDsl

import static com.querydsl.core.types.dsl.DateTimeExpression.currentTimestamp;
import static com.querydsl.jpa.JPAExpressions.select;
import static com.querydsl.jpa.JPAExpressions.selectFrom;
...
QPartner partner = QPartner.partner;
QCompany company = QCompany.company;
QAd ad = QAd.ad;

from(partner)
    .where(predicate)
    .select(new QAccessPartner(
        partner,
        select(company.count())
            .from(company)
            .where(company.block.eq(false))
            .where(company.partner.eq(partner)),
        select(ad.count())
            .from(ad)
            .where(ad.company.in(
                selectFrom(company)
                   .where(company.block.eq(false))
                   .where(company.partner.eq(partner))))
            .where(ad.adStatus.eq(AdStatus.APPROVAL))
            .where(ad.adStartDate.before(currentTimestamp()))
            .where(ad.adEndDate.after(currentTimestamp()))));

Sql

SELECT p.*,
       (SELECT COUNT(*) 
          FROM company 
         WHERE block_yn = 'N' 
           AND partner_no = p.partner_no) AS company_count,
       (SELECT COUNT(*) FROM ad
         WHERE company_no IN
             (SELECT company_no FROM company 
               WHERE block_yn = 'N' 
                 AND partner_no = p.partner_no)
           AND ad_status = 'APPROVAL'
           AND ad_start_date < CURRENT_TIMESTAMP AND ad_end_date > CURRENT_TIMESTAMP
       ) AS AD_COUNT
  FROM partner p
 WHERE // predicate to sql

CASE-WHEN subquery in projection

// TODO

Subquery in WHERE Clause: in

QueryDsl

import static com.querydsl.core.types.dsl.DateTimeExpression.currentTimestamp;
import static com.querydsl.jpa.JPAExpressions.selectFrom;
...
BooleanBuilder predicates = new BooleanBuilder();
predicates.and(
    company.ads.contains(
        selectFrom(ad)
            .where(ad.adStatus.eq(AdStatus.APPROVAL)
                .and(ad.adStartDate.before(currentTimestamp()))
                .and(ad.adEndDate.after(currentTimestamp())))));

Sql

...
WHERE ad IN (
    SELECT ad_no FROM ad iad
     WHERE iad.ad_status = 'APPROVAL'
       AND iad.ad_start_date > CURRENT_TIMESTAMP
       AND iad.ad_end_date < CURRENT_TIMESTAMP
)

Subquery in WHERE Clause: = (with max)

QueryDsl

import static com.amp.magazine.shared.repository.querydsl.ZonedDateTimeExpressions.currentTimestamp;
import static com.querydsl.jpa.JPAExpressions.select;
...
QCuratorRecommend cr = QCuratorRecommend.curatorRecommend;
BooleanBuilder predicates = new BooleanBuilder();
predicates.and(cr.recommendId.eq(
    select(cr.recommendId.max())
        .from(cr)
        .where(cr.openTs.loe(currentTimestamp())    // this <= right
            .and(cr.open.eq(true))
            .and(cr.saveTypeCode.eq(SaveTypeCode.REAL))
              )));

Sql

WHERE recommend_id = (
    SELECT MAX(recommend_id) 
      FROM recommend cr
     WHERE cr.open_ts <= CURRENT_TIMESTAMP
       AND cr.open_yn = 'Y'
       AND cr.save_type_code = 'REAL'
)

Impossible

• From 절에 서브쿼리
• Union

JPASQLQuery를 이용하면 가능하나, 그럴거면 그냥 Native Query를 쓰는 것을 추천