CODEWITHSUNDEEP
spring-boothibernatejpaquerydsl
kaiserspectre
kaiserspectre

Reputation: 13

GroupBy query with Querydsl and Spring Boot

I have two entities, one is Client and the other is Transactions and they are linked @OneToMany respectively.

I would like to create a query using Querydsl that will filter my clients based on the count of their transactions. Basically I want this SQL query

SELECT * FROM client WHERE id IN (SELECT client_id
FROM (SELECT client_id, count(client_id) as 'count' from transaction group by client_id) as t
where t.count >= 10)

to be done in Querydsl. The problem is I want to return a BooleanExpression so I can aggregate the query further, i.e AND it or OR it with some other query.

The Querydsl entities are QClient and QTransaction respectively.

This is my current code,

    QClient client = QClient.client1;
    QTransaction transaction = QTransaction.transaction;
    var count = Expressions.numberPath(Long.class, "count");
    var subquery = JPAExpressions.select(transaction.client.id, transaction.client.id.count().as(count))
        .from(transaction).groupBy(transaction.client.id).where(count.gt(depositCount)).select(transaction.client.id);
    return client.id.in(subquery);

It doesn't work in this form.

Upvotes: 1

Views: 2165

Answers (1)

gtiwari333
gtiwari333

Reputation: 25146

How about this?

Note this is in context to my test project at https://github.com/gtiwari333/spring-boot-web-application-seed. But you can easily apply the same for your tables.

Ref: https://github.com/gtiwari333/spring-boot-web-application-seed/blob/master/main-app/src/main/java/gt/app/modules/article/ArticleRepositoryCustomImpl.java#L48

    public void doQuery() {
        QArticle qArticle = QArticle.article;
        QUser user = QUser.user;

        var subquery1 = JPAExpressions
            .select(qArticle.createdByUser.id)
            .from(qArticle)
            .groupBy(qArticle.createdByUser.id)
            .having(qArticle.id.count().gt(5));

        BooleanExpression exp = user.id.in(subquery1);

        BooleanExpression exp2 = qArticle.title.length().lt(15);

        List<Article> ar = from(qArticle)
            .join(user).on(qArticle.createdByUser.id.eq(user.id))
            .select(qArticle)
            .where(exp.and(exp2))
            .fetch();

        System.out.println(ar);
    }

Also, your query can be simplified.

SELECT * FROM client WHERE id IN 
( SELECT client_id FROM 
        (SELECT client_id, count(client_id) as 'count' from ransaction group by client_id)
 as t where t.count >= 10)

to:

SELECT * FROM client
WHERE id IN ( SELECT client_id
            FROM
                TRANSACTION
            GROUP BY
                client_id
            HAVING
                count(client_id)> 10)

Upvotes: 1

Related Questions