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.

crm86 · Accepted Answer · 2016-08-17 07:43:57Z

3

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));

edited Aug 17, 2016 at 7:43

answered Aug 16, 2016 at 11:30

crm86

1,3942 gold badges22 silver badges45 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

crm86 Over a year ago

where sum(stock0_.qty)>=120 is a group function. This should work with a having() comparison or use a JPASubquery.

Anchit Pancholi Over a year ago

can you please explain me in detail or provide any link. How to use that with pagable

Anchit Pancholi Over a year ago

I am using Using spring org.springframework.data.domain.Pagable class, for pagination. look like Above example using two different table.

Anchit Pancholi Over a year ago

How can i run below query in querydsl :-

select count(*) as count  from (select count(stock0_.stock_id) as col_0_0_ from  stock stock0_  having sum(stock0_.QTY)>=100) tbl;

crm86 Over a year ago

Definitely, the final query implementation is not a part of the first question. Your error was a common misunderstanding of query aggregation in QueryDSL. If possible, mark the answer as valid if it was helpful for you.

|

Collectives™ on Stack Overflow

Spring Data JPA with QueryDSL, Count issue with aggregate function

1 Answer 1

6 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Your Answer

Sign up or log in

Post as a guest

Related