How to get total average value from subquery average collection using querydsl

Question

I have the next entity:

@Entity
@Table(name = "search_request_items")
public class SearchRequestItem extends LongIdEntity {
        
   @Column(name = "date")
   private Instant date;
       
   @ManyToOne(fetch = FetchType.LAZY)
   @JoinColumn(name = "user_id")
   private User user;
        
   @Column(name = "result_count")
   private Long resultCount;
        
   /**
    * Request's text.
    */
   @Column(name = "request")
   private String request;
        
   /**
    * Request's quality. It may take 0 or 1.
    */
   @Column(name = "quality")
   private Integer quality;

   ...
}

Then i have the next queryDSL query, which return collection of quality avg and user count grouped by request' text:

public JPAQuery<Tuple> prepareTotalQuery() {
    QSearchRequestItem requestItem = QSearchRequestItem.searchRequestItem;
    QUser user = QUser.user;

    NumberExpression<Double> qualityAvgExpression = requestItem.quality.avg();
    NumberExpression<Long> qualityCountExpression = requestItem.user.countDistinct();

    JPAQuery<Tuple> query = queryFactory
        .select(qualityAvgExpression, qualityCountExpression)
        .from(requestItem)
        .leftJoin(requestItem.user, user)
        .groupBy(requestItem.request)
        .having(qualityAvgExpression.isNotNull(),
            qualityCountExpression.gt(2));

    return query;
}

But i need to return total avg upon this collection just like this native query:

select avg(n1.avg_quality) 
from (select count(distinct user_id), avg(quality) as avg_quality 
      from search_request_items
      group by request
      having avg(quality) is not null and count(distinct user_id) > 2
     ) n1;

So, how to update my querydsl query to get this result?

score 2 · Accepted Answer · answered Sep 02 '21 at 09:25

This issue here is that you're using JPA and JPA doesn't allow to use subqueries as join target in the from clause.

Blaze-Persistence is an extension of JPA and integrates well with Hibernate. It adds Common Table Expressions and subselect (even lateral) joins to JPQL. Blaze-Persistence also has a Querydsl integration, allowing you to write a query like the following:

List<Number> fetch = new BlazeJPAQuery<>(entityManager, cbf)
     .with(cteType, new BlazeJPAQuery<>()
         .bind(cteType.avgQuantity, requestItem.quality.avg())
         .from(requestItem)
         .leftJoin(requestItem.user, user)
         .groupBy(requestItem.request)
         .having(qualityAvgExpression.isNotNull(), qualityCountExpression.gt(2))))
     )
    .select(cteType.avgQuantity.avg())
    .from(cteType)         
   .fetch();

However, with plain JPA and Hibernate, there is no simple way to do this.

Provided that you're only averaging a set of numbers though, which are not intensive to serialize over JDBC and do not suffer from potential N+1 issues, I'd suggest to simply do the final average step in memory:

queryFactory
        .select(qualityCountExpression)
        .from(requestItem)
        .leftJoin(requestItem.user, user)
        .groupBy(requestItem.request)
        .having(qualityAvgExpression.isNotNull(),
            qualityCountExpression.gt(2))
        .stream()
        .collect(Collectors.averagingDouble(i -> i.doubleValue()))

How to get total average value from subquery average collection using querydsl

1 Answers1