Search code examples
spring-data-jpaquerydsl

How in Querydsl for Spring Data JPA do I find those entitities that have at least one child satisfying several conditions?

I have the following relationships as part of my model:

enter image description here

I am trying to use the Querydsl functionality in Spring Data JPA to find all engagements where the associated student has a tenancy in a given residence within a given date range. I tried the following to return the appropriate BooleanExpression that could be combined with others and supplied to EngagementRepository.findAll():

public BooleanExpression inResidence(Residence residence, LocalDate startDate, LocalDate endDate) {
    final QTenancy tenancies = QEngagement.engagement.student.tenancies.any();
    return tenancies.residence.eq(residence)
        .and(tenancies.startDate.loe(endDate))
        .and(tenancies.endDate.goe(startDate));
}

However the resulting SQL contains independent EXISTS subqueries for each of the three conditions – residence, start date and end date; that is, as it stands each condition could be satisfied by a different tenancy when what I am after is for a qualifying tenancy to satisfy all three conditions. I realise I have probably misunderstood the purpose of any() and suspect that I need to make use of Querydsl's subqueries, but I am not quite sure how to, especially since some examples I have found are for version 3 and things appear to have changed in version 4.

Solution

  • This is what I managed to come up with for using subqueries in Querydsl 4. The generated SQL has just one EXISTS subquery that tests all three conditions.

    public BooleanExpression inResidence(Residence residence, LocalDate startDate, LocalDate endDate) {
    final QTenancy tenancy = QTenancy.tenancy;
    return QEngagement.engagement.student.tenancies.any().in(
        JPAExpressions.selectFrom(tenancy).where(
            tenancy.residence.eq(residence)
                .and(tenancy.startDate.loe(endDate))
                .and(tenancy.endDate.goe(startDate))
        )
    );
}