Querydsl with predicate multiple conditions on parent and child table

Viewed 458

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...

0 Answers
Related