I'm using springboot with java 8, querydsl 4.2.1
I've a structure from database where, a person table containing name, address, active_status. A person can have multiple kyc documents with their validity. KYC documents are in kyc_document table.
I need to get the active persons, where their kyc documents are valid on today's date.
Below is my code.
QPerson qPerson = QPerson.person;
QKycDOcument qKycDOcument = QKycDOcument.kycDOcument;
Predicate datePredicate = JPAExpressions
.selectOne()
.from(qKycDOcument)
.where(qKycDOcument.fromDate.before(new Date(System.currentTimeMillis())),
qKycDOcument.toDate.isNull().or(qKycDOcument.toDate.after(new Date(System.currentTimeMillis())))
)
.exists();
builder.and(qPerson.activeStatus.eq("Active").and(datePredicate));
It gives me below query -
select
person0_.RECORD_ID as RECORD_I1_7_,
person0_.ACTIVE_STATUS as ACTIVE_S2_7_,
person0_.DECEASED as DECEASED3_7_,
person0_.GENDER as GENDER4_7_,
person0_.PROFILE_NOTES as PROFILE_5_7_,
person0_.RECORD_TYPE as RECORD_T6_7_
from
PERSON person0_
where
person0_.ACTIVE_STATUS=?
and (
exists (
select
1
from
KYC_DOCUMENT kycdoc1_
where
kycdoc1_.FROM_DATE<?
and (
kycdoc1_.TO_DATE is null
or kycdoc1_.TO_DATE>?
)
)
)
order by
person0_.RECORD_ID desc
my expected query is :
select
person0_.RECORD_ID as RECORD_I1_7_,
person0_.ACTIVE_STATUS as ACTIVE_S2_7_,
person0_.DECEASED as DECEASED3_7_,
person0_.GENDER as GENDER4_7_,
person0_.PROFILE_NOTES as PROFILE_5_7_,
person0_.RECORD_TYPE as RECORD_T6_7_
from
PERSON person0_
where
person0_.ACTIVE_STATUS=?
and (
exists (
select
1
from
KYC_DOCUMENT kycdoc1_
where
person0_.RECORD_ID = kycdoc1_.RECORD_ID
and kycdoc1_.FROM_DATE<?
and (
kycdoc1_.TO_DATE is null
or kycdoc1_.TO_DATE>?
)
)
)
order by
person0_.RECORD_ID desc
person0_.RECORD_ID = kycdoc1_.RECORD_ID is the additional condition in the inner query, which is the requirement and not achieved.
Please suggest, what should be in my predicate builder.
Thanks for the suggestions coming in...