QueryDsl SubQuery - redutan/redutan.github.io GitHub Wiki
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를 쓰는 것을 추천