title: How to fetch only columns that satisfy the condition in one-to-many relationship in QueryDSL?
body: <p>I have two following entities:</p>
  <pre><code>@Getter
  @Setter
  @NoArgsConstructor
  @AllArgsConstructor
  @Entity
  @Table(name = &quot;affiliate_programs&quot;)
  @SequenceGenerator(name = AbstractEntity.GENERATOR, sequenceName = &quot;affiliate_programs_seq&quot;, allocationSize = 1)
  public class AffiliateProgram extends AbstractAuditableDeletableEntity {
  
      private static final int DESCRIPTION_LENGTH = 512;
  
      @Column(nullable = false)
      private String title;
  
      @OneToMany(mappedBy = &quot;affiliateProgram&quot;, fetch = FetchType.LAZY)
      private Set&lt;AffiliateProgramStatistics&gt; 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;
          }
      }
  }
  </code></pre>
  <pre><code>@Getter
  @Setter
  @NoArgsConstructor
  @AllArgsConstructor
  @Entity
  @Table(name = &quot;affiliate_programs_statistics&quot;)
  @SequenceGenerator(name = AbstractEntity.GENERATOR, sequenceName = &quot;affiliate_programs_statistics_seq&quot;, 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
      }
  }
  </code></pre>
  <p>I am trying to fetch only the columns from <code>AffiliateProgramStatistics</code> that match the SQL between condition. My SQL query looks like this:</p>
  <pre><code>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';
  </code></pre>
  <p>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.</p>
  <p>I tried to write that query in QueryDSL and that's what I came up with:</p>
  <pre><code>@Repository
  @RequiredArgsConstructor
  public class AffiliateProgramsCustomRepositoryImpl implements AffiliateProgramsCustomRepository {
  
      private final EntityManager entityManager;
  
      @Override
      public Page&lt;AffiliateProgram&gt; search(final AffiliateProgramSearchForm form) {
          final QAffiliateProgram affiliateProgram = QAffiliateProgram.affiliateProgram;
          final QAffiliateProgramStatistics affiliateProgramStatistics = QAffiliateProgramStatistics.affiliateProgramStatistics;
          final JPAQuery&lt;AffiliateProgram&gt; query = new JPAQuery&lt;AffiliateProgram&gt;(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);
      }
  }
  </code></pre>
  <pre><code>public class AffiliateProgramsRepositoryHelper extends RepositoryHelper {
  
      public static Predicate[] getPredicates(final AffiliateProgramSearchForm form) {
          final QAffiliateProgramStatistics affiliateProgramStatistics = QAffiliateProgramStatistics.affiliateProgramStatistics;
          final List&lt;Predicate&gt; predicates = new ArrayList&lt;&gt;();
          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]);
      }
  }
  </code></pre>
  <p>But the result of this is not satisfying. If at least one of the columns in <code>AffiliateProgramStatistics</code> match the <code>between()</code> condition, it fetches every single column from the table that matches tje <code>leftJoin()</code> condition.</p>
  <p>How can I fetch only the columns that I need?</p>
  <p>P.S. Hibernate generates the following query:</p>
  <pre><code>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 ?
  </code></pre>
  <p>which works perfectly and fetches only the data I need if i run it in console</p>