How to fetch only columns that satisfy the condition in one-to-many relationship in QueryDSL?

Question

I have two following entities:

@Getter
@Setter
@NoArgsConstructor
@AllArgsConstructor
@Entity
@Table(name = "affiliate_programs")
@SequenceGenerator(name = AbstractEntity.GENERATOR, sequenceName = "affiliate_programs_seq", allocationSize = 1)
public class AffiliateProgram extends AbstractAuditableDeletableEntity {

    private static final int DESCRIPTION_LENGTH = 512;

    @Column(nullable = false)
    private String title;

    @OneToMany(mappedBy = "affiliateProgram", fetch = FetchType.LAZY)
    private Set<AffiliateProgramStatistics> statistics;

    public enum SortType implements ISortType {
        ID(QAffiliateProgram.affiliateProgram.id),
        TITLE(QAffiliateProgram.affiliateProgram.title),

        @Getter
        private ComparableExpressionBase[] expressions;

        SortType(final ComparableExpressionBase... expressions) {
            this.expressions = expressions;
        }
    }
}

@Getter
@Setter
@NoArgsConstructor
@AllArgsConstructor
@Entity
@Table(name = "affiliate_programs_statistics")
@SequenceGenerator(name = AbstractEntity.GENERATOR, sequenceName = "affiliate_programs_statistics_seq", allocationSize = 1)
public class AffiliateProgramStatistics extends AbstractAuditableEntity {

    @ManyToOne(fetch = FetchType.LAZY, optional = false, cascade = CascadeType.ALL)
    private AffiliateProgram affiliateProgram;

    @Enumerated(EnumType.STRING)
    private EventType eventType;

    private LocalDate date;

    public enum EventType {
        MERCHANTS,
        PRIORITY_MERCHANTS,
        COUPONS,
        CLICKS
    }
}

I am trying to fetch only the columns from AffiliateProgramStatistics that match the SQL between condition. My SQL query looks like this:

select *
from affiliate_programs ap
         left join affiliate_programs_statistics aps on ap.id = aps.affiliate_program_id
where ap.deleted = false and aps.date between '2020-07-20' and '2020-08-20';

And the result of this query is exactly what I need - I get only columns that are NOT marked as deleted AND columns with date BETWEEN required dates.

I tried to write that query in QueryDSL and that's what I came up with:

@Repository
@RequiredArgsConstructor
public class AffiliateProgramsCustomRepositoryImpl implements AffiliateProgramsCustomRepository {

    private final EntityManager entityManager;

    @Override
    public Page<AffiliateProgram> search(final AffiliateProgramSearchForm form) {
        final QAffiliateProgram affiliateProgram = QAffiliateProgram.affiliateProgram;
        final QAffiliateProgramStatistics affiliateProgramStatistics = QAffiliateProgramStatistics.affiliateProgramStatistics;
        final JPAQuery<AffiliateProgram> query = new JPAQuery<AffiliateProgram>(entityManager)
                .distinct()
                .from(affiliateProgram)
                .leftJoin(affiliateProgram.statistics, affiliateProgramStatistics)
                .where(AffiliateProgramsRepositoryHelper.getPredicates(form))
                .orderBy(AffiliateProgramsRepositoryHelper.getOrders(form.getSorting()))
                .limit(form.getLimit())
                .offset(form.getOffset());

        return AffiliateProgramsRepositoryHelper.pageBy(query, form);
    }
}

public class AffiliateProgramsRepositoryHelper extends RepositoryHelper {

    public static Predicate[] getPredicates(final AffiliateProgramSearchForm form) {
        final QAffiliateProgramStatistics affiliateProgramStatistics = QAffiliateProgramStatistics.affiliateProgramStatistics;
        final List<Predicate> predicates = new ArrayList<>();
        final String formattedQuery = form.getFormattedQuery();

        if (!isNullOrEmpty(formattedQuery)) {
            predicates.add(affiliateProgramStatistics.affiliateProgram.title.likeIgnoreCase(formattedQuery));
        }

        predicates.add(affiliateProgramStatistics.date.between(form.getFrom(), form.getTo()));
        predicates.add(affiliateProgramStatistics.affiliateProgram.deleted.isFalse());

        return predicates.toArray(new Predicate[0]);
    }
}

But the result of this is not satisfying. If at least one of the columns in AffiliateProgramStatistics match the between() condition, it fetches every single column from the table that matches tje leftJoin() condition.

How can I fetch only the columns that I need?

P.S. Hibernate generates the following query:

Hibernate: 
select distinct 
  affiliatep0_.id as id1_0_, 
  affiliatep0_.created_date_time as created_2_0_, 
  affiliatep0_.last_modified_date_time as last_mod3_0_, 
  affiliatep0_.deleted as deleted4_0_, 
  affiliatep0_.clicks_count as clicks_c5_0_, 
  affiliatep0_.coupons_count as coupons_6_0_, 
  affiliatep0_.description as descript7_0_, 
  affiliatep0_.merchants_count as merchant8_0_, 
  affiliatep0_.priority_merchants_count as priority9_0_, 
  affiliatep0_.priority_order as priorit10_0_, 
  affiliatep0_.title as title11_0_ 
from affiliate_programs affiliatep0_ 
  inner join affiliate_programs_statistics statistics1_ on affiliatep0_.id=statistics1_.affiliate_program_id 
  cross join affiliate_programs affiliatep2_ 
  where statistics1_.affiliate_program_id=affiliatep2_.id 
    and (statistics1_.date between ? and ?) 
    and affiliatep2_.deleted=? 
  order by affiliatep0_.title desc nulls last limit ?

which works perfectly and fetches only the data I need if i run it in console

Answer 1

JPA supports the ON clause in JPQL since 2.1, and QueryDSL is able to generate that ON clause in queries. Hibernate had a precedessor for the ON clause in the form of the now deprecated WITH clause. The ON clause can be used in more occasions.

Just use .on(Predicate) immediately after the join on which it should be applied:

    final JPAQuery<AffiliateProgram> query = new JPAQuery<AffiliateProgram>(entityManager)
            .distinct()
            .from(affiliateProgram)
            .leftJoin(affiliateProgram.statistics, affiliateProgramStatistics)
            .on(AffiliateProgramsRepositoryHelper.getPredicates(form))
            .orderBy(AffiliateProgramsRepositoryHelper.getOrders(form.getSorting()))
            .limit(form.getLimit())
            .offset(form.getOffset());