Spring Data JPA with QueryDSL, Count issue with aggregate function

Question

I am using Spring Data JPA with QueryDSL and trying to use Sum function in where condition, As i am using pagination so i have to get count first. So i have java code like below :-

NumberPath<Double> path = entityPath.getNumber("qty", Double.class);
BooleanExpression exp = path.sum().loe(120);        
JPQLQuery countQuery = from(stock).where(exp);
long count = countQuery.count();

its creating query like this :-

select count(stock0_.stock_id) as col_0_0_ from stock stock0_ 
where sum(stock0_.qty)>=120;

and i am getting Error Code: 1111. Invalid use of group function.

above query is not working in SQL as well because sum function cant be use with count in where condition. I have not idea how to deal with such problem when i have to get count first and then fetch the real data. Can someone please help me out with what is JPA approach to deal with such issue.

Please don't suggested @Query annotation because i can not use it. Due to dynamic filtration requirement.

Answer 1

You are using aggregate functions (sum). Then you need having() instead where()

This example is a really large one. You only need to care about pagination and having elements.

If you got different predicates and a pageRequest object.

Page request example:

pageRequest = new PageRequest(
                MI_PAGE, 
                LIMIT, 
                Sort.Direction.valueOf( ASC ), 
                ORDER_FIELD);

EntityQ represents a QueryDSL generated meta-entity.

Query example:

new JPAQuery(em).from(entityQ).where( entityQ.id.in(

        new JPASubQuery()
        .from(entity2Q).innerJoin(entity2Q.objEntityQ, entityQ)
            .where(ENTITY, PREDICATE)                           
            .groupBy(entity2Q.objEntityQ.id)
            .having( Wildcard.count.goe(SIZE) )                 
        .list(entity2Q.objEntityQ.id)

    ))
    .offset(pageRequest.getOffset() )
    .limit( pageRequest.getPageSize() ) 
    .orderBy(ORDERS).list(entityQ); 

EDIT 2 More specific for your example:

I usually have a persistence context in my custom repositories:

@PersistenceContext
EntityManager em

Then, you could create a simple JPAQuery:

new JPAQuery(em)
.from(YOUR_STOCK_QUERYDSL_ENTITY)
.groupBy(YOUR_STOCK_QUERYDSL_ENTITY.field_to_sum)
.having(YOUR_STOCK_QUERYDSL_ENTITY.field_to_sum.sum().as("alias")
.goe(100));